Macro Sum Formula For a Variable Rang
 

Hi All,

Can some one please help me on how to fix code below to sum a variable
range? I am not sure if my sum formula is at fault or my whole logic got to
go.

Sub Question()
x = 0
c = 1

'To delete empty rows in the sheet
'
lastrow = ActiveSheet.UsedRange.Rows.count
Application.ScreenUpdating = False
For R = lastrow To 1 Step -1
If Application.WorksheetFunction.CountA(Rows(R)) = 0 _
Then Rows(R).Delete
c = c + 1
Next R

'To Add sum formulas on account rows


Do While x < c
x = x + 1
Dim BEGROW As Integer
Dim ENDROW As Integer
If Cells(x, 1) Like "Account:" Then
BEGROW = x + 1
End If

If Cells(x, 1) Like "Totals:" Then
Cells(x, 5).Select
ENDROW = ActiveCell.Row - 1
Selection.FormulaR1C1 = "=Sum(R"&BEGROW"C5:R"&ENDROW"C5)"
End If
Loop

Macro Sum Formula For a Variable Rang
 

Selection.FormulaR1C1 = "=Sum(R"&BEGROW"C5:R"&ENDROW"C5)"
should be

Selection.FormulaR1C1 = "=Sum(R"& BEGROW & _
"C5:R" & ENDROW & "C5)"

would be a start.

--
Regards,
Tom Ogilvy


"Sid" wrote in message
...
Hi All,

Can some one please help me on how to fix code below to sum a variable
range? I am not sure if my sum formula is at fault or my whole logic got
to
go.

Sub Question()
x = 0
c = 1

'To delete empty rows in the sheet
'
lastrow = ActiveSheet.UsedRange.Rows.count
Application.ScreenUpdating = False
For R = lastrow To 1 Step -1
If Application.WorksheetFunction.CountA(Rows(R)) = 0 _
Then Rows(R).Delete
c = c + 1
Next R

'To Add sum formulas on account rows


Do While x < c
x = x + 1
Dim BEGROW As Integer
Dim ENDROW As Integer
If Cells(x, 1) Like "Account:" Then
BEGROW = x + 1
End If

If Cells(x, 1) Like "Totals:" Then
Cells(x, 5).Select
ENDROW = ActiveCell.Row - 1
Selection.FormulaR1C1 = "=Sum(R"&BEGROW"C5:R"&ENDROW"C5)"
End If
Loop

Macro Sum Formula For a Variable Rang
 

Thank you Tom; Just What I needed.



"Sid" wrote:

Hi All,

Can some one please help me on how to fix code below to sum a variable
range? I am not sure if my sum formula is at fault or my whole logic got to
go.

Sub Question()
x = 0
c = 1

'To delete empty rows in the sheet
'
lastrow = ActiveSheet.UsedRange.Rows.count
Application.ScreenUpdating = False
For R = lastrow To 1 Step -1
If Application.WorksheetFunction.CountA(Rows(R)) = 0 _
Then Rows(R).Delete
c = c + 1
Next R

'To Add sum formulas on account rows


Do While x < c
x = x + 1
Dim BEGROW As Integer
Dim ENDROW As Integer
If Cells(x, 1) Like "Account:" Then
BEGROW = x + 1
End If

If Cells(x, 1) Like "Totals:" Then
Cells(x, 5).Select
ENDROW = ActiveCell.Row - 1
Selection.FormulaR1C1 = "=Sum(R"&BEGROW"C5:R"&ENDROW"C5)"
End If
Loop