Need help making formula run in vba

KAZSTREBOR

New Member
Joined
Feb 18, 2021
Messages
7
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,346
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,346
Office Version
  1. 365
Platform
  1. Windows
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!).
 
Solution

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top