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

 

[LloydFinancials]

This may work for what you need:

Sub something()
   lastrow = Cells(Rows.Count, 1).End(xlUp).Row
   lastcol = Cells(8, Columns.Count).End(xlToLeft).Column
   For i = 8 To lastrow
      For j = 24 To lastcol
         If Cells(7, j).Value = Cells(i, 1).Value Then
         Cells(i, j).Value = (Cells(i, 5).Value * Cells(i, 10).Value) / Application.SumIf(Range("A8:A" & lastrow), Cells(7, j).Value, Range("E8:E" & lastrow))
         End If
      Next j
   Next i
End Sub

Luke

 

[navidadi28]

Thank you Lloyd for the attempt, however, this does not return anything. My original code would do the sumif and then sum the figures and paste it into an above cell. Perhaps if I explain my code it will help.

'the first cell being calculated in
Range("X8").Select
'If A8 = x& (same laon name) then give me the sum of the values of those l divided by the number of times we offered that loan.
ActiveCell.FormulaR1C1 = _
"=IF(RC1=R7C,(RC5*RC10)/SUMIF(R8C1:R1107C1,R7C,R8C5:R1107C5),"""")"
Selection.AutoFill Destination:=Range("X8:X1107")

'this is just an auto fill down IT IS HERE THAT I WANT TO MAKE IT AUTOFILL TO THE LAST CELL not a fixed cell
Range("X8:X1107").Select
Selection.AutoFill Destination:=Range("X8:AJ1107"), Type:=xlFillDefault

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

'auto filling across columns
Selection.AutoFill Destination:=Range("X3:AJ3"), Type:=xlFillDefault
Range("X3:AJ3").Select



So really the main part I need assistance on is on the IF/SUMif statement as well as when I sum the ranges. How Do I makes those drynamic ranges. I REALLY appreciate your help!

 

[LloydFinancials]

I was able to get the same results as your code for every respective cell, but with the flexibility of dynamic row count, so I am not sure why you get nothing. Maybe the "Cells" and "Range" references need to be prefixed by the worksheet.

 

[navidadi28]

Hi Lloyd thank you for the quick response. The part that you wrote, does that only replace my SUMIF statement? I use your code then my code starting with the autofill? For example:

lastrow = Cells(Rows.Count, 1).End(xlUp).Row
lastcol = Cells(8, Columns.Count).End(xlToLeft).Column
For i = 8 To lastrow
For j = 24 To lastcol
If Cells(7, j).Value = Cells(i, 1).Value Then
Cells(i, j).Value = (Cells(i, 5).Value * Cells(i, 10).Value) / Application.SumIf(Range("A8:A" & lastrow), Cells(7, j).Value, Range("E8:E" & lastrow))
End If
Next j
Next i

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]

This may work for what you need:

Sub something()
   lastrow = Cells(Rows.Count, 1).End(xlUp).Row
   lastcol = Cells(8, Columns.Count).End(xlToLeft).Column
   For i = 8 To lastrow
      For j = 24 To lastcol
         If Cells(7, j).Value = Cells(i, 1).Value Then
         Cells(i, j).Value = (Cells(i, 5).Value * Cells(i, 10).Value) / Application.SumIf(Range("A8:A" & lastrow), Cells(7, j).Value, Range("E8:E" & lastrow))
         End If
      Next j
   Next i
End Sub

Luke

Could you please explain your code?

 

[LloydFinancials]

Sub something()
   lastrow = Cells(Rows.Count, 1).End(xlUp).Row
   lastcol = Cells(8, Columns.Count).End(xlToLeft).Column
   For i = 8 To lastrow
      For j = 24 To lastcol '24 is column X
         If Cells(7, j).Value = Cells(i, 1).Value Then 'If X7 = A8 then X8 = E8*J8/SUMIF(A8:A100[for instance], X7, E8:E100)
         Cells(i, j).Value = (Cells(i, 5).Value * Cells(i, 10).Value) / Application.SumIf(Range("A8:A" & lastrow), Cells(7, j).Value, Range("E8:E" & lastrow))
         End If
      Next j 'Next j would be 25. Cells(7, j) is Y7. If Y7 = A8 then Y8 = E8*...
   Next i 'The next i would be row 9 and the process would start over.
End Sub

Hope this helps. It helps me to break down code into its parts to better understand.

 

[LloydFinancials]

Sub something() is the only code you need.

 

[Sense-A]

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).

 

[navidadi28]

Sub something() is the only code you need.

Thank you for the breakdown that makes sense. But 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)