Sum of rows Column wise - Strange behaviour
 

I am trying to sum all the rows in a range column wise

Values Start from G9 and ends at Rx (x is the last row num)
The values get populated based on a selection from a list.
The below code works perfect when i change the selection once.
But when i change the selection again, the first row (i.e 9th gets
filled with #REF)


With Sheets("Sheet1")
col = "G"
For i = 1 To 12
Range(col & (x + 1)).Cells.Formula = "=Sum(" & col & "9:" &

col & x & ")"
col = Chr(Asc(col) + 1)
Next i
End With


Any thoughts?


Regards
Thiaga

Sum of rows Column wise - Strange behaviour
 

thiaga, What if the For Loop used for ?

Rick


"thiaga" wrote in message
oups.com...
I am trying to sum all the rows in a range column wise

Values Start from G9 and ends at Rx (x is the last row num)
The values get populated based on a selection from a list.
The below code works perfect when i change the selection once.
But when i change the selection again, the first row (i.e 9th gets
filled with #REF)


With Sheets("Sheet1")
col = "G"
For i = 1 To 12
Range(col & (x + 1)).Cells.Formula = "=Sum(" & col & "9:" &

col & x & ")"
col = Chr(Asc(col) + 1)
Next i
End With


Any thoughts?


Regards
Thiaga

Sum of rows Column wise - Strange behaviour
 

I have 12 columns (G thru R) for which i need the sum to be calculated.

Each time i do a Ascii + 1 to get the column name.

Sum of rows Column wise - Strange behaviour
 

I have 12 columns (G thru R) for which i need to calculate the sum.

So i use a for loop to get the column name, instead of having nested
for!
I do a ascii + 1 for getting G,H, I,J,....

Ideally it should be

For each column
sum = 0
For each row in the column
sum = sum + cell_value
End row
Display sum
End column

Sum of rows Column wise - Strange behaviour
 

thiaga, try this code, I beleve this is what you are looking for. If not it
should give you a good start..

hth , Rick

Sub Macro1()
Dim lrow As Long
Dim i As Integer

With Sheets("Sheet1")
For i = 7 To 18 ' columns g thru r

' find last cell in column, get row no#
lrow = .Cells(9, i).End(xlDown).Row '< Substitue here for your last row
' now set formula in lastrow + 1
.Cells(lrow + 1, i).Formula = _
"=Sum(" & Chr(64 + i) & "9:" & Chr(64 + i) & lrow & ")"
Next i
End With

End Sub



"thiaga" wrote in message
oups.com...
I have 12 columns (G thru R) for which i need to calculate the sum.

So i use a for loop to get the column name, instead of having nested
for!
I do a ascii + 1 for getting G,H, I,J,....

Ideally it should be

For each column
sum = 0
For each row in the column
sum = sum + cell_value
End row
Display sum
End column