Dynamic SUMIF Statement

[navidadi28]

hello,new to VBA so I was hoping someone could help me out. I've looked through this website and online and can't find a solution.

The below Macro will run on different tabs so it will go through columns of different row numbers. Therefore, I need to make my SUMIF and ranges Dynamic so they will account for all the rows. I simply recorded a macro and worked through it and have hit a dead end. PLEASE someone help a guys out!

Range("X8").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC1=R7C,(RC5*RC10)/SUMIF(R8C1:R1107C1,R7C,R8C5:R1107C5),"""")"
Selection.AutoFill Destination:=Range("X8:X1107")
Range("X8:X1107").Select
Selection.AutoFill Destination:=Range("X8:AJ1107"), Type:=xlFillDefault
Range("X3").Select
ActiveCell.FormulaR1C1 = "=SUM(R[5]C:R[1104]C)"
Selection.AutoFill Destination:=Range("X3:AJ3"), Type:=xlFillDefault
Range("X3:AJ3").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

 

[navidadi28]

You could do it without VBA by using formulas such as Address() Indirect() Row() Column() nested inside the sumif formula so that the cell references are dynamic.

For example, here is a dynamic sum formula i used:

=SUM(INDIRECT(ADDRESS(14,COLUMN(),4)):INDIRECT(ADDRESS(ROW()-1,COLUMN(),4)))

I knew the data headers would always be row 14. So I used vba to find the last row, and had the row of data containing the sum formulas automatically copied to row beneath last row of data in each worksheet. So you could modify it for a sumif function if you always know what row the header row will be (likely row 1).

Thank you Sense-A but if I am not mistaken this formula would require to me constantly re-enter the formula? the purpose of the macro is to remove me having to recalculate for each month moving forward. Also, after I have my sums the macro transfers the values into a different spreadsheet.

 

[navidadi28]

Sub something() is the only code you need.

Thank you Lloyd for the breakdown. I am still learning on cell references (24=X etc.) But where I am confused is if sub something() is all I need what about this part:

'in cell X3 summing the rows X8 to last row.
Range("X3").Select
ActiveCell.FormulaR1C1 = "=SUM(R[5]C:R[1104]C)"

(this is performed for all columns from X to AJ)

Also, for a lil more background the sum from this row is then transferred to the final workbook (via a different macro).

Thank you for your help!

 

[navidadi28]

AH HA! I completed the code for the Sum part.

I did:

Range("X3").Select
ActiveCell.Formula = "=Sum(X8:X" & lastrow & ")"

BIG TIME THANK YOU!!