# Need help making formula run in vba

#### KAZSTREBOR

##### New Member
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!

### Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

#### 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:
VBA Code:
``````    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!).

Replies
0
Views
60
Replies
2
Views
91
Replies
9
Views
113
Replies
0
Views
56
Replies
4
Views
31

1,136,794
Messages
5,677,779
Members
419,720
Latest member
kurman

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back