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
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