Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Strange if(***) behaviour? | Excel Discussion (Misc queries) | |||
Sum on rows column wise - Strange error | Excel Worksheet Functions | |||
Strange behaviour when sorting rows | Excel Discussion (Misc queries) | |||
Data row wise, formula column wise | Excel Discussion (Misc queries) | |||
Strange behaviour | Excel Worksheet Functions |