need dynamic name in SUM

cliffndonna

New Member
Joined
Apr 21, 2017
Messages
1
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
ActiveWorkbook.Names.Add Name:="top", RefersToR1C1:=ActiveCell
botCell = "C" & iCntr - 1
sumCell = "C" & iCntr
Range(botCell).Select
routineName2 = "bot" & prevcell
ActiveWorkbook.Names.Add Name:="bot", RefersToR1C1:=ActiveCell
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
ActiveWorkbook.Names.Add Name:="topB", RefersToR1C1:=ActiveCell
botCellB = "B" & iCntr - 1
sumCellB = "B" & iCntr
Range(botCellB).Select
routineName2 = "botB" & prevcellB
ActiveWorkbook.Names.Add Name:="botB", RefersToR1C1:=ActiveCell
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
ActiveWorkbook.Names.Add Name:="top", RefersToR1C1:=ActiveCell
botCell = "C" & iCntr - 1
sumCell = "C" & iCntr
Range(botCell).Select
routineName2 = "bot" & prevcell
ActiveWorkbook.Names.Add Name:="bot", RefersToR1C1:=ActiveCell
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
ActiveWorkbook.Names.Add Name:="topB", RefersToR1C1:=ActiveCell
botCellB = "B" & iCntr - 1
sumCellB = "B" & iCntr
Range(botCellB).Select
routineName2 = "botB" & prevcellB
ActiveWorkbook.Names.Add Name:="botB", RefersToR1C1:=ActiveCell
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
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
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
 
Upvote 0

Forum statistics

Threads
1,213,563
Messages
6,114,329
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top