CODEWITHSUNDEEP
arraysvbaexcelloops
Majid Javanmard
Majid Javanmard

Reputation: 127

Excel VBA : Is it possible to get address of each cell in a loop and use them out of loop

I want to write a loop through some sheets of a workbook to get address of one specific cell in each sheet. My purpose is to get the address of these cells and use them as a reference out of loop.

I wrote a code but it does not work as what I want :

Sub RegionalAverage()

    For i = 1 To 2
    Sheets(i).Activate
    Range("A1").Select
    Selection.AutoFilter
    ActiveSheet.Range("A1:H23393").AutoFilter Field:=6, Criteria1:=1
    Columns("A:H").Select
    Selection.SpecialCells(xlCellTypeVisible).Select
    Selection.Find(What:="1/1/2008", After:=ActiveCell, LookIn:=xlFormulas, _
            LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False).Select
    ActiveCell.Offset(0, 4).Select
    Name (i) = "'" & Selection.Parent.name & "'" & "!" & Selection.Address(External:=False)

    Next i

    MsgBox Name(1)
    MsgBox Name(2)

End Sub

Upvotes: 2

Views: 893

Answers (1)

user4039065
user4039065

Reputation:

I've rewritten your procedure to avoid using .Select¹ and used the Option Explicit² environment to force declaration of the variables used. I suspect manby of your problems originated with the use of the undeclared and undimensioned Name array.

Option Explicit

Sub RegionalAverage()
    'declare the variables you plan to use!!!!!
    Dim i As Long, fnd As Range, aNames As Variant

    'you were only looking for two addresses so dimension the array now
    ReDim aNames(1 To 2)

    'loop through the first two worksheets
    For i = 1 To 2
        'start isolating the workspace ujsing With ... End With
        With Worksheets(i)
            'if AutoFilter is active, turn it off
            If .AutoFilterMode Then .AutoFilterMode = False
            ''work with the 'island' of data radiating out from A1
            With .Cells(1, "A").CurrentRegion
                'isolate to A:H
                With .Resize(.Rows.Count, 8)
                    'filter on column F = 1
                    .AutoFilter Field:=6, Criteria1:=1
                    'isolate to the visible cells
                    With .SpecialCells(xlCellTypeVisible)
                        'set a range object to the first found cell
                        Set fnd = .Cells.Find(What:="1/1/2008", After:=.Cells(.Cells.Count), _
                                              LookIn:=xlFormulas, LookAt:=xlPart, _
                                              SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                                              MatchCase:=False, SearchFormat:=False)
                        'check if anything was found
                        If Not fnd Is Nothing Then
                            'offset 4 columns to the right
                            Set fnd = fnd.Offset(0, 4)
                            'store the parent worksheet name and cell address
                            aNames(i) = Chr(39) & fnd.Parent.Name & "'!" & fnd.Address(External:=False)
                        End If
                    End With
                End With
            End With
        End With
    Next i

    MsgBox "First found at " & aNames(1) & vbLf & _
           "Second found at " & aNames(2)

End Sub

Note that my array is called aNames. Name is considered a 'reserved word' in VBA and it is not considered 'best practise' to repurpose reserved words, methods or properties as variables.

.¹ See How to avoid using Select in Excel VBA macros for more methods on getting away from relying on select and activate to accomplish your goals.

² Setting Require Variable Declaration within the VBE's Tools ► Options ► Editor property page will put the Option Explicit statement at the top of each newly created code sheet. This will avoid silly coding mistakes like misspellings as well as influencing you to use the correct variable type in the variable declaration. Variables created on-the-fly without declaration are all of the variant/object type. Using Option Explicit is widely considered 'best practice'.

Upvotes: 2

Related Questions