In Loop Store Cell Address for Later Use

[AlexaS]

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

 

[circledchicken]

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]

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.

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:

[TABLE="width: 500"]
<TBODY>[TR]
[TD]Location
[/TD]
[TD]Vendor
[/TD]
[TD]#
[/TD]
[/TR]
[TR]
[TD]A
[/TD]
[TD]1
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]A
[/TD]
[TD]2
[/TD]
[TD]6
[/TD]
[/TR]
[TR]
[TD]A
[/TD]
[TD]3
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]A
[/TD]
[TD]Total Vendors
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B
[/TD]
[TD]2
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]B
[/TD]
[TD]4
[/TD]
[TD]4
[/TD]
[/TR]
[TR]
[TD]B
[/TD]
[TD]7
[/TD]
[TD]7
[/TD]
[/TR]
[TR]
[TD]B
[/TD]
[TD]8
[/TD]
[TD]9
[/TD]
[/TR]
[TR]
[TD]B
[/TD]
[TD]Total Vendors
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]C
[/TD]
[TD]1
[/TD]
[TD]4
[/TD]
[/TR]
[TR]
[TD]C
[/TD]
[TD]Total Vendors
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]All Branches
[/TD]
[TD]All Vendors
[/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]


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:

=C5+C11+C14+(etc)+...

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

 

[pjmorris]

Hi Alexas,

My solution would be to use an array:

Dim SumAddresses(20) as Range
Dim n as integer

n=1

Start loop

SumAddresses='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]

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:

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

Hi Alexas,

My solution would be to use an array:

Dim SumAddresses(20) as Range
Dim n as integer

n=1

Start loop

SumAddresses='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]

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:

    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]

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