If cell equals a product and date, input formula in intersect cell VBA

2
votes
                    B   C   D
                   1/1 1/2 1/3

     2 Product 1    ?
     3 Product 2

I'm trying to use VBA to automate inserting formula based on product name and date criteria. Tried multiple ways including index match, intersect but can't seem to get it to work!

Currently my formula is manually placed based on dates of another worksheet. Formula for reference

=IFERROR(IF(VLOOKUP($B$1&$C39&Japan!$E39,TDM!$A:$AA,23,0)=Japan!U$33,SUMIF(Allocation!$A:$A,Japan!$A39,Allocation!$S:$S),"")*$G39,"")

Is there a way to automate this using VBA?

1
vbaexcelexcel-formula

1 Answers

1
votes

Try this :

Sub Fill_Formula()
Dim StartRow As Long
StartRow = 2

For i = StartRow To 50 'Start and End Rows
    For j = 2 To 10 'Start and End Columns
        Cells(i, j).Formula = _
            "IFERROR(IF(VLOOKUP($B$1&$C" & 39 + (i - StartRow) & "&Japan!$E" & 39 + (i - StartRow) & ",TDM!$A:$AA,23,0)=Japan!U$" & 33 + (i - StartRow) & ",SUMIF(Allocation!$A:$A,Japan!$A" & 39 + (i - StartRow) & ",Allocation!$S:$S),"")*$G" & 39 + (i - StartRow) & ","")"
    Next j
Next i


End Sub