Using VBA to write formula with variable range

[PGD15]

Hi there,

For this example lets say I have two sheets, "Summary" and "Data".

In my Summary Sheet in column B I want write a sumifs formula where the value in column A (Summary) if it appears in "Data" sheet column M is summed and drag and drop this down to the last row where column A is populated as not to affect the formula i'll need to lock the range too. I think the below achieves this (i tried with absolute cell reference and it works).

Worksheets("Summary").Range("B3:B" & Cells(Rows.Count, "A").End(xlUp).Row).FormulaR1C1 = "=SUMIFS('Data'!M:!M,'Data'!M:!M,Summary!RC1)"

However my issue is, I don't really wish to use !M:!M as I have many of these formula I need to run and querying every single cell is ineffective. in my Data sheet the columns the data will appear in is fixed so it will always be column M however the row numbers change. it will always start in row 2 but i do not know the end point. is there a way i can count the rows in 'Data sheet column M' then store this as a variable and use this variable as the range?

Ie something like

dim Example as Integer
set example = Worksheets("Data").Range("M3:M" & Cells(Rows.Count, "A").End(xlUp).Row)
Worksheets("Summary").Range("B3:B" & Cells(Rows.Count, "A").End(xlUp).Row).FormulaR1C1 = "=SUMIFS('Data'!M2:'Example','Data'!M2:!Example,Summary!RC1)"

Thanks in advance

 

[6StringJazzer]

You are on the right track. BTW you don't need that second "!" in your range reference.

You also have a bug in your original code. You should qualify Cells and Rows if you are qualifying Range. I used a With for this. If it was working it was just by luck of where you put the code or what sheet is active.

Dim LastMRow As Long

With Worksheets("Data")
   Set LastMRow = .Cells(.Rows.Count, "M").End(xlUp).Row
End With

With Worksheets("Summary")
   .Range("B3:B" & .Cells(.Rows.Count, "A").End(xlUp).Row).FormulaR1C1 = _
      "=SUMIFS('Data'!M2:M" & LastMRow & ",'Data'!M2:M" & LastMRow & ",Summary!RC1)"
End With

 

[PGD15]

You are on the right track. BTW you don't need that second "!" in your range reference.

You also have a bug in your original code. You should qualify Cells and Rows if you are qualifying Range. I used a With for this. If it was working it was just by luck of where you put the code or what sheet is active.

Dim LastMRow As Long

With Worksheets("Data")
   Set LastMRow = .Cells(.Rows.Count, "M").End(xlUp).Row
End With

With Worksheets("Summary")
   .Range("B3:B" & .Cells(.Rows.Count, "A").End(xlUp).Row).FormulaR1C1 = _
      "=SUMIFS('Data'!M2:M" & LastMRow & ",'Data'!M2:M" & LastMRow & ",Summary!RC1)"
End With

I should say i did have a line before my code above making the work sheet active as i did come across it being temperamental and couldn't figure out why so made the sheet active and it worked .


I've had to edit the above to change:

   Set LastMRow = .Cells(.Rows.Count, "M").End(xlUp).Row

to

LastMRow = .Cells(.Rows.Count, "M").End(xlUp).Row

or i had a compile error: object not found.

However this now kinda works. VBA i think is fine but i think my formula is wrong.

it prints the following (cell reference correct based on my current test data) however the ' around some of the cell reference prevent it from working
=SUMIFS('Data'!'M2':'M17','Data'!'T2':'T17',Summary!$A3)
it should print as:
=SUMIFS('Data'!M2:M17,'Data'!T2:T17,Summary!$A3)

Have i done something stupid i can't spot?

 

[6StringJazzer]

The compiler error you found was my fault and you made the needed correction.

The problem is mixing A1 notation in an R1C1 formula. I did not catch that the first time. Here is corrected code:

Dim LastMRow As Long

With Worksheets("Data")
   LastMRow = .Cells(.Rows.Count, "M").End(xlUp).Row
End With

With Worksheets("Summary")
   .Range("B3:B" & .Cells(.Rows.Count, "A").End(xlUp).Row).FormulaR1C1 = _
      "=SUMIFS('Data'!R2C13:R" & LastMRow & "C13,'Data'!R2C13:R" & LastMRow & "C13,Summary!RC1)"
End With

 

[PGD15]

Thanks, i had to update the last part to use column 20 (T) as that was my criteria range but works perfectly many thanks!

"=SUMIFS('Data'!R2C13:R" & LastMRow & "C13,'Data'!R2C20:R" & LastMRow & "C20,Summary!RC1)"