Thanks:  0
Likes:  0

# Thread: need dynamic name in SUM

1. ## need dynamic name in SUM

I have only started yesterday writing excel macros. I have a spreadsheet that has 2 columns with lists of numbers with breaks, I need to sum the columns. The code below works, but if you hit recalculate it uses the totals from the last to columns and replaces all the others through the page. Is there a way to dynamically name my "top", "topB", "bot" and "botB" so that the formulas will remain unique for every instance of the formulas?

any help would be appreciated

For iCntr = 1 To lRow Step 1
If Not IsEmpty(Cells(iCntr, "F").Value) Then
Rows(iCntr).Insert Shift:=xlDown
Application.CutCopyMode = False
If iCntr > 1 Then
prevcell = prevcell + 1
Range("C" + CStr(prevcell)).Select
routineName1 = "top" & prevcell
botCell = "C" & iCntr - 1
sumCell = "C" & iCntr
Range(botCell).Select
routineName2 = "bot" & prevcell
Range(sumCell).Select
ActiveCell.FormulaR1C1 = "=SUM(top:bot)"
End If
prevcell = iCntr
If iCntr > 1 Then
prevcellB = prevcellB + 1
routineName1 = "topB" & prevcellB
Range("B" + CStr(prevcellB)).Select
botCellB = "B" & iCntr - 1
sumCellB = "B" & iCntr
Range(botCellB).Select
routineName2 = "botB" & prevcellB
Range(sumCellB).Select
ActiveCell.FormulaR1C1 = "=SUM(topB:botB)"
Range("A" + CStr(iCntr)).Value = "Totals"
Range("A" + CStr(iCntr)).EntireRow.Font.Bold = True
End If
prevcellB = iCntr
iCntr = iCntr + 1
lMax = lMax + 1
End If
Next
lMax = lMax + lRow
For iCntr = lBeginMax To lMax Step 1
If Not IsEmpty(Cells(iCntr, "F").Value) Then
Rows(iCntr).Insert Shift:=xlDown
Application.CutCopyMode = False
If iCntr > 1 Then
prevcell = prevcell + 1
Range("C" + CStr(prevcell)).Select
routineName1 = "top" & prevcell
botCell = "C" & iCntr - 1
sumCell = "C" & iCntr
Range(botCell).Select
routineName2 = "bot" & prevcell
Range(sumCell).Select
ActiveCell.FormulaR1C1 = "=SUM(top:bot)"
End If
prevcell = iCntr
If iCntr > 1 Then
prevcellB = prevcellB + 1
Range("B" + CStr(prevcellB)).Select
routineName1 = "topB" & prevcellB
botCellB = "B" & iCntr - 1
sumCellB = "B" & iCntr
Range(botCellB).Select
routineName2 = "botB" & prevcellB
Range(sumCellB).Select
ActiveCell.FormulaR1C1 = "=SUM(topB:botB)"
Range("A" + CStr(iCntr)).Value = "Totals"
Range("A" + CStr(iCntr)).EntireRow.Font.Bold = True
End If
prevcellB = iCntr
iCntr = iCntr + 1
End If
Next

2. ## Re: need dynamic name in SUM

Hi cliffndonna

Your code seems to sum columns F and G and write them out in column B, as well as the title "Total".

Can I suggest a different way of getting your totals?

Using End(xlUp) we can go to the last row of a column and then look Up to the first cell that isn't empty and get the row number - the last row in your range (usually).

We can them use that row number in our VBA:

Code:
```Sub myTotals()
Dim BotF As Integer
Dim BotG As Integer

BotF = Range("F" & Rows.Count).End(xlUp).Row
BotG = Range("G" & Rows.Count).End(xlUp).Row

Range("a1") = "Total"
Range("a2") = "Total"

Range("b1") = "=SUM(F1" & ":F" & BotF & ")"
Range("b2") = "=SUM(G1" & ":G" & BotG & ")"
End Sub```
Or am off track?

Regards
Alan

3. ## Re: need dynamic name in SUM

If you are summing based on criteria, why not just use SUMIFS()?