Modification to existing IF Conditional VBA code to check for four Criteria

[AnilR]

Hi

This is my first posting; so please excuse me if I'm not doing it right.

I found the below VBA code, written by someone in Australia. I like this VBA code as it works real well based on the author's design. It computes the Cost of Goods sold (COGS) based on three (3) criteria: 1) Month, 2) Product, and 3) Quantity Sold. It appears that by specifying the “month” criteria, along with the other two criteria, it computes all products sold for that “month” regardless of the number of years in which that month occurs. In other words, my understanding of it in layman term is as follows: Compute all COGS in the month(s) of July only whether or not goods were sold in say, July 2015, July 2016, and July 2017. So, if 20 goods were sold in July 2015, 50 goods were sold in July 2016; and 70 goods were sold in July 2017, the code will compute the COGS for quantity 20, 50, and 70.

I don’t know much about VBA and would like and appreciate some help in modifying the below VBA code to do the following:

1. Compute the COGS between two specified dates

So, it will operate as follows:

If “From_date” => “date1” and “To_date” <= “date2” compute the COGS between those two specified spreadsheet dates, “date1” and “date2”

“date2’ will have to be a new criteria in my Spreadsheet in addition of the other 3 criteria.

Function FIFO2(ByRef Data, ByVal Dte As Long, ByVal Product As String, ByVal Stock As Double) As Double

  Dim ar As Variant

  Dim i As Long

  Const DateCol As Long = 1

  Const ProdCol As Long = 2

  Const QtyCol  As Long = 3

  Const CostCol As Long = 4



  ar = Data



  For i = LBound(ar, 1) To UBound(ar, 1)

  If Month(ar(i, DateCol)) = Month(Dte) Then

  If ar(i, ProdCol) = Product Then

  If Stock < ar(i, QtyCol) Then

  FIFO2 = FIFO2 + Stock * ar(i, CostCol)

  Exit Function

  Else

  FIFO2 = FIFO2 + (ar(i, QtyCol) * ar(i, CostCol))

  Stock = Stock - ar(i, QtyCol)

  If Stock <= 0 Then Exit Function

  End If

  End If

  End If

  Next


End Function

Thanks a million in advance to any or all who may respond. Please attach your Spreadsheet, showing your Code and sample data used

 

[AnilR]

I'm sorry, I should have posted a sample file with the VBA code and some data.
Please see attached file.

 

[p45cal]

a guess:

Function FIFO2(ByRef Data, ByVal FromDte As Long, ByVal ToDte As Long, ByVal Product As String, ByVal Stock As Double) As Double
Dim ar As Variant
Dim i As Long
Const DateCol As Long = 1
Const ProdCol As Long = 2
Const QtyCol As Long = 3
Const CostCol As Long = 4
ar = Data

For i = LBound(ar, 1) To UBound(ar, 1)
  If ar(i, DateCol) >= FromDte And ar(i, DateCol) <= ToDte Then
    If ar(i, ProdCol) = Product Then
      If Stock < ar(i, QtyCol) Then
        FIFO2 = FIFO2 + Stock * ar(i, CostCol)
        Exit Function
      Else
        FIFO2 = FIFO2 + (ar(i, QtyCol) * ar(i, CostCol))
        Stock = Stock - ar(i, QtyCol)
        If Stock <= 0 Then Exit Function
      End If
    End If
  End If
Next
End Function

 

[AnilR]

Thanks P45cal. I'll give it a try.
Question:
Because of my lack of understanding of VBA, where in my spreadsheet do I specify the Fromdte and Todte?

 

[p45cal]

I don't know where our data are on the sheet, but as an example:

 

[AnilR]

Ok. Got it! Thanks.
I'm testing your solution. I've added a new column to enter the "Todte" in my spreadsheet. So far, it looks like your "guess" is working.
I will let you know.
Much appreciation for your help. ..Cheers!

 

[p45cal]

Examples in cells K14:K16 of attached (your file).

 

[AnilR]

Examples in cells K14:K16 of attached (your file).

Hi Therep45cal,

Thank you very much! It works really well on the dates I've tested thus far. I will adopt it for my Spreadsheet. ..I've attached the spreadsheet here to show your selection of dates (From & To), incorporated into mine.

This FIFO will be very useful when doing my 2016 Taxes.

A challenge for you if you have the time:

Is it possible to reverse the same VBA code so that it does LIFO calculation? LIFO is Last in, First out. In other words, goods bought last are sold first.

I've seen some LIFO calculator; but they use Macro instead; so it is difficult to set up your data to work with Macro type LIFO calculators

There are situations from time to time where one may purchase a stock at a very low price while previously bought stock were bought at a higher price. So to make a quick profit when the market goes up above the higher price paid previously, one may want to sell what was bought last.

Once again, Thank you very much. I really appreciate your jumping in so quickly to help me out. ..You are the best!