Need help making formula run in vba

[KAZSTREBOR]

Hi, I have a forumla that I need to run in VBA as part of a larger macro. It needs to put the formula in column "N" for all the entries in the spreadsheet. I'm not sure how to make this happen, any help is greatly appreciated. The formula is =IF(COUNTIFS($B5:B$5,B5,$F5:F$5,F5)=1,SUMIFS($D$5:$D$10000,$B$5:$B$10000,B5,$F$5:$F$10000,F5,$E$5:$E$10000,E5),"") How would I get this to work using VBA. Thanks!

 

[Joe4]

If you turn on your Macro Recorder, and record yourself entering the formula into Excel, you will get the code you need to enter this formula in via VBA.
You can then edit the VBA to tell it exactly which range to apply it to.

If it needs to be dynamic, and we need to figure out the last row to apply it to "on-the-fly", what is the best column for us to look at to determine where the last row of data is?
Would column B work, or is there a better one to use?

 

[Joe4]

If column B will work, this will do that you want:

    Dim lr As Long
    
'   Find last row in column B with data
    lr = Cells(Rows.Count, "B").End(xlUp).Row
    
'   Enter formula in all data rows in column N starting on row 5
    Range("N5:N" & lr).FormulaR1C1 = _
        "=IF(COUNTIFS(RC2:R5C[-12],RC[-12],RC6:R5C[-8],RC[-8])=1,SUMIFS(R5C4:R10000C4,R5C2:R10000C2,RC[-12],R5C6:R10000C6,RC[-8],R5C5:R10000C5,RC[-9]),"""")"

Note that the last line of code is what the Macro Recorder will get for you (this is a great tool, as you can make Excel do some of the hard work for you!).