In Loop Store Cell Address for Later Use

AlexaS

Board Regular
Joined
Oct 12, 2012
Messages
155
Hello,

I've been searching for a way to do this for a little while. What I am trying to do is during a loop operation, which subtotals variable ranges, I want to store the locations of the cells that it puts the sum function into. I.E. if based on criteria it determines that range E4:E12 is summed into E13, I need to save E13 to use in a formula once I'm out of the loop. I don't know how many instances it will find and there's the possibility in the future that not only will the number of instances increase, but the location could always be different as well. I'm really hoping there is a way to do this without a million lines of code (because my best solution would be to use holders but that could get VERY long).

Thanks,

Alex
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

circledchicken

Well-known Member
Joined
Aug 13, 2011
Messages
2,932
Hi,

Can you provide a more detailed example of exactly what you are trying to do? Including sample inputs and outputs and any code you already have?
 

AlexaS

Board Regular
Joined
Oct 12, 2012
Messages
155
Here is the code I have. This code works. I've been trying to find a way to record the cell address every time that it loops, so that it can be used in a sum formula at the bottom of the sheet.

Code:
Sub ResetSubTotals()
'Reset RptByVendor formulas
    'Recreate subtotal lines
    Dim R As Long
    Range("B4:B300").Select
    
    If ActiveSheet.Name = "RptByVendor" Then
        R = Sheets("RptByVendor").UsedRange.Rows.Count + 1
        If Sheets("RptByVendor").Range("B4") = "" Then
            R = 1
        End If
        Set c = Selection.Find(What:="Total Branches", After:=ActiveCell, LookIn:= _
                    xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext _
                    , MatchCase:=False, SearchFormat:=False)
            c.Activate
        If Not c Is Nothing Then
            firstAddress = c.Address
            Do
                R = R + 1
                Totals
                Set c = Cells.FindNext(After:=ActiveCell)
                c.Activate
            Loop While Not c Is Nothing And c.Address <> firstAddress
        End If
    ElseIf ActiveSheet.Name = "Intermediate" Then
        R = Sheets("RptByVendor").UsedRange.Rows.Count + 1
        If Sheets("RptByVendor").Range("B4") = "" Then
            R = 1
        End If
        Set c = Selection.Find(What:="Total Branches", After:=ActiveCell, LookIn:= _
                    xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext _
                    , MatchCase:=False, SearchFormat:=False)
            c.Activate
        If Not c Is Nothing Then
            firstAddress = c.Address
            Do
                R = R + 1
                Totals
                Set c = Cells.FindNext(After:=ActiveCell)
                c.Activate
            Loop While Not c Is Nothing And c.Address <> firstAddress
        End If
    Else: On Error Resume Next
    End If
    
End Sub
Sub Totals()
'Reset RptByVendor & Intermediate totals and copy across
    
    Dim StartingCell As String
    Dim LastCellInSum As String
    Dim FirstCellInSum As String
    
    ActiveCell.Offset(0, 1).Activate
    
    StartingCell = ActiveCell.Address(False, False)
    LastCellInSum = ActiveCell.Offset(-1, 0).Address(False, False)
    
    Do
        ActiveCell.Offset(-1, 0).Select
    Loop Until ActiveCell.Value = "" Or ActiveCell.Value = "#"
    
    FirstCellInSum = ActiveCell.Offset(1, 0).Address(False, False)
    Range(StartingCell).Activate
    ActiveCell.Formula = "=sum(" & FirstCellInSum & ":" & LastCellInSum & ")"
    
    If ActiveSheet.Name = "Intermediate" Then
        ActiveCell.Select
        Selection.Copy
        Selection.AutoFill Destination:=Range(ActiveCell, ActiveCell.Offset(0, 9)), Type:=xlFillDefault
        ActiveCell.Offset(0, -2).Activate
    ElseIf ActiveSheet.Name = "RptByVendor" Then
        ActiveCell.Copy
        Union(ActiveCell.Offset(, 1), ActiveCell.Offset(, 3), ActiveCell.Offset(, 4) _
            , ActiveCell.Offset(, 6), ActiveCell.Offset(, 7), ActiveCell.Offset(, 9), ActiveCell.Offset(, 10) _
            , ActiveCell.Offset(, 12), ActiveCell.Offset(, 13), ActiveCell.Offset(, 15), ActiveCell.Offset(, 16) _
            , ActiveCell.Offset(, 18), ActiveCell.Offset(, 19), ActiveCell.Offset(, 21), ActiveCell.Offset(, 22) _
            , ActiveCell.Offset(, 24), ActiveCell.Offset(, 25), ActiveCell.Offset(, 27), ActiveCell.Offset(, 28) _
            , ActiveCell.Offset(, 30), ActiveCell.Offset(, 31), ActiveCell.Offset(, 33), ActiveCell.Offset(, 34) _
            ).PasteSpecial Paste:=xlPasteFormulas
        ActiveCell.Offset(0, -2).Activate
    Else: On Error Resume Next
    End If
End Sub

The way the report is set up you have:

Location
Vendor
#
A
1
5
A
2
6
A
3
2
A
Total Vendors
B
2
2
B
4
4
B
7
7
B
8
9
B
Total Vendors
C
1
4
C
Total Vendors
All Branches
All Vendors

<TBODY>
</TBODY>


There are more lines of data going across, but this is the main part. The loop is finding the "Total Vendors" and then determining how many lines are included in each sum function. So A would sum 3 lines, B would sum 4 and C would sum 1. This program is being run when the branch or vendors have been updated, either added or removed.

In the "All Branches - All Vendors" row, I need to have a formula adding each instance of "Total Vendors". So in the above example, I would need to input a formula that reads:
Code:
=C5+C11+C14+(etc)+...

However, the next time it gets updated it could be C5, C14 and C29.
 

pjmorris

Well-known Member
Joined
Aug 2, 2012
Messages
1,991
Office Version
  1. 2016
Platform
  1. Windows
Hi Alexas,

My solution would be to use an array:

Dim SumAddresses(20) as Range
Dim n as integer

n=1

Start loop

SumAddresses(n)='range of interest'

n=n+1

end loop

Clearly the above isn't the fully worked up solution, but it should give you an idea of how to do this. If you need more cells remembered than the 20 I've suggested (haven't disected your code) then you could Redim the array.

Hope this helps.
 

FormR

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,535
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

In the "All Branches - All Vendors" row, I need to have a formula adding each instance of "Total Vendors". So in the above example, I would need to input a formula that reads:
Code:
=C5+C11+C14+(etc)+...

However, the next time it gets updated it could be C5, C14 and C29.

I don't really follow what your code is doing, but instead of "=C5+C11+C14+(etc)" couldn't you simply have "=Sum(C5:C29)/2"
 

AlexaS

Board Regular
Joined
Oct 12, 2012
Messages
155
Hi Alexas,

My solution would be to use an array:

Dim SumAddresses(20) as Range
Dim n as integer

n=1

Start loop

SumAddresses(n)='range of interest'

n=n+1

end loop

Clearly the above isn't the fully worked up solution, but it should give you an idea of how to do this. If you need more cells remembered than the 20 I've suggested (haven't disected your code) then you could Redim the array.

Hope this helps.

Just a couple of questions: is this adding the cell values? Also - what would I put as the "range of interest"? Would it be the column that I am trying to sum or would it be the column that I am finding off of?

Thanks!
 

AlexaS

Board Regular
Joined
Oct 12, 2012
Messages
155
I don't really follow what your code is doing, but instead of "=C5+C11+C14+(etc)" couldn't you simply have "=Sum(C5:C29)/2"

The code is looping through the report, finding the instance of "Total Vendors", moving over to the column that needs to be summed, determining the number of rows that need to be included, and then the formula is being copied across the columns. So In the example, I had one column that needed to be summed. In the actual report (one of them) I have nine columns next to each other, and in another I have however many activecell.offsets I had in the Union.

I can't believe I didn't think about something so simple! Thank you! Just goes to show how the more you think the more you can overcomplicate things :)

Here is the code that I am going to use:

Code:
    Columns("C:C").Select
    Selection.Find(What:="All Vendors", After:=ActiveCell, LookIn:=xlValues, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Offset(0, 2).Activate
    lr = ActiveCell.Offset(-2, 0).Address(False, False)
    ActiveCell.Value = "=Sum(E4:" & lr & ")/2"
    Selection.AutoFill Destination:=Range(ActiveCell, ActiveCell.Offset(0, 9)), Type:=xlFillDefault
 

pjmorris

Well-known Member
Joined
Aug 2, 2012
Messages
1,991
Office Version
  1. 2016
Platform
  1. Windows
Hi AlexaS,

Delighted that you've found an answer, and it seems a lot simpler than the coding solution.

Just to answer your query. All my modifications do are store the addresses that you need to sum, so in your very first post it would Store "E13" (that is the 'range of interest'). You would then loop through the array to build your final sum function. Hope this makes some sense.

Best Regards
 

Forum statistics

Threads
1,136,322
Messages
5,675,083
Members
419,549
Latest member
EliteBeat

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
Top