Compile Error: Expected end of Statement using SUM formula in variable range loop

[Scotherng]

Hi there,

New to this forum and I love it! I have searched and searched but can't seem to find an answer that resolves my problem. I am using a loop in a macro that groups and subtotals columns for data categories. The data the macro is run on varies greatly in size each time, but the columns are constant. My categories are in column B, and my subtotal column is G. (see partial code below). I need the formula for the subtotals to remain because data may be manually updated after the macro is run, so the worksheetfunction.sum option does not work since that returns the value only. I can't seem to get the SUM formula to work right when I specify the variable range. The code below only includes up to the line where I call the sum formula...everything else is working great. I can't seem to find a way to specify the range that the formula will accept:

Dim myRow As Long
Dim MyStart As Long

MyStart = 2
myRow = 3 'or use 2 if you haven't got a header


Do Until Cells(myRow, 1) = ""
If Cells(myRow, 2) = Cells(myRow - 1, 2) Then
myRow = myRow + 1
Else
Cells(myRow, 1).EntireRow.Insert
Cells(myRow, 7).Formula = "=SUM(Range("G" & MyStart & ":G" & myRow - 1)) " ...this turns the whole line red and returns the compile error.

I have also tried this:
Cells(myRow, 7).Formula = "=SUM(Range(Cells(MyStart, 7), Cells(myRow - 1, 7)))"...this runs but I get the #NAME? error in the cell.

And I've tried this as well:
Cells(myRow, 7).Formula = "=SUM(Range(""G"" & MyStart & "":G"" & myRow - 1)) " ...this runs but again I get the #NAME? error in the cell.

What is interesting is that I have no problem simply selecting the range with this code:
Range("G" & MyStart & ":G" & myRow - 1).Select

I know there has to be a simple solution but my rookie skills are not helping. Any ideas?

Thank you!

 

[JoeMo]

Try:

Cells(myRow, 7).Formula = "=SUM(G" & MyStart & ":G" & myRow - 1 & ")"

 

[Scotherng]

Joe that worked great! Thank you so much for the quick response. If it's not too much to ask, could you give me a brief explanation on that or refer me to a thread that explains it?

Thanks again!

 

[JoeMo]

Joe that worked great! Thank you so much for the quick response. If it's not too much to ask, could you give me a brief explanation on that or refer me to a thread that explains it?

Thanks again!

You are welcome. It's just a matter of separating the VBA variables from the rest of the formula using quote marks. Say myStart (the VBA variable) is 1. Then VBA sees the cell G1 as Range("G" & myStart), but Excel sees the same range simply as G1 (Excel has no idea what myStart is). So, if you want VBA to write a worksheet formula to a cell that refers to cell G1 (let's say the formula is: =G1), you can't use "= G & myStart" in the formula that goes into the cell - it has to be written "= G" & myStart to allow VBA to apply the current value of the variable myStart.

 

[Scotherng]

Cool. I see the difference now. Very helpful! Thanks again!

 

[JoeMo]

Cool. I see the difference now. Very helpful! Thanks again!

You are welcome - thanks for the reply.