Convert Sumproduct to VBA

[carlleese24]

Hi

I have 2 sheets one is called the price file which has the following columns
Customer (Col A)
Material (Col B)
Price (Col C)
Currency (Col D)
Start date (Col E)
End date (Col F)

The 2nd called the invoice details which has the following columns
Bill Doc (Col A)
Item (Col B)
Material (Col C)
Material number (Col D)
Created on (Col E)
Invoiced quantity (Col F)
SU (Col G)
Net Value (Col H)
Currency (Col I)
Price (Col J)
Customer (Col K)


I have a formulae in column L

=SUMPRODUCT(--('price File'!$B$2:$B$11='Invoice Details'!E2),--('Invoice Details'!G2<=('price File'!$F$2:$F$11)),--('Invoice Details'!G2>='price File'!$E$2:$E$11),('price File'!$C$2:$C$11))

What this formulae does is takes the date and the material code on the invoice details sheet and then looks through the list on the price file sheet and if the date is within the start and end date and matches the material code on a particular row it will then take the price on that row.


and also another one in column M

=SUMPRODUCT(--('price File'!$B$2:$B$11='Invoice Details'!E2),--('Invoice Details'!G2<=('price File'!$F$2:$F$11)),--('Invoice Details'!G2>='price File'!$E$2:$E$11),('price File'!$A$2:$A$11))

What this formulae does is takes the date and the material code on the invoice details sheet and then looks through the list on the price file sheet and if the date is within the start and end date and matches the material code on a particular row it will then take the customer name on that row.

I would to convert this process into a VBA code somehow so it makes the process faster as I will have thousands of rows

Please could anyone help me to make this possible.

Carl

 

[AlphaFrog]

Sub Lookups()

    Dim wsPrice As Worksheet, wsInv As Worksheet
    Dim Lastrow&, i&, j&
    Dim Price, Inv, LM()
    
    Set wsPrice = Sheets("price File")
    Set wsInv = Sheets("Invoice Details")
    
    Lastrow = wsPrice.Cells.Find("*", , xlValues, , xlByRows, xlPrevious).Row
    Price = wsPrice.Range("A2:F" & Lastrow)
    
    Lastrow = wsInv.Cells.Find("*", , xlValues, , xlByRows, xlPrevious).Row
    Inv = wsInv.Range("A2:K" & Lastrow)
    ReDim LM(1 To UBound(Inv, 1), 1 To 2)
    
    For i = 1 To UBound(Inv, 1)
        For j = 1 To UBound(Price, 1)
            'SUMPRODUCT(--('price File'!$B$2:$B$11='Invoice Details'!E2),--('Invoice Details'!G2<=('price File'!$F$2:$F$11)),--('Invoice Details'!G2>='price File'!$E$2:$E$11),('price File'!$C$2:$C$11))
            'SUMPRODUCT(--('price File'!$B$2:$B$11='Invoice Details'!E2),--('Invoice Details'!G2<=('price File'!$F$2:$F$11)),--('Invoice Details'!G2>='price File'!$E$2:$E$11),('price File'!$A$2:$A$11))
            If (Price(j, 2) = Inv(i, 5) And Price(j, 6) >= Inv(i, 7) And Price(j, 5) <= Inv(i, 7)) Then
                LM(i, 1) = Price(j, 3): LM(i, 2) = Price(j, 1)    'Result from wsPrice columns C and A
                Exit For
            End If
    Next j, i
    
    wsInv.Range("L2:M" & Lastrow) = LM
    
End Sub

 

[carlleese24]

Hi


Many thanks for that just what I wanted.

How would I modify the code so instead of looking for the prices with the criteria it will add them up.


Carl