Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: need dynamic name in SUM

  1. #1
    New Member
    Join Date
    Apr 2017
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

  2. #2
    New Member
    Join Date
    Feb 2014
    Location
    Melbourne AU
    Posts
    48
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #3
    Board Regular FDibbins's Avatar
    Join Date
    Feb 2013
    Location
    Duncansville, PA USA
    Posts
    5,994
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: need dynamic name in SUM

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

    - Posting guidelines, forum rules and terms of use

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    Regards

    Ford

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
This website uses cookies
We use cookies to store session information to facilitate remembering your login information, to allow you to save website preferences, to personalise content and ads, to provide social media features and to analyse our traffic. We also share information about your use of our site with our social media, advertising and analytics partners.
     


DMCA.com