Excel VBA - Selecting range using For each loop

Question

Hello

I am learning VBA and was trying a macro for following objective:

I want to create a new collection and assign range items to it based on specific criteria. The criteria: Identify cells with a specific value (user specified) in all worksheets in active workbook and add those to new collection.

I entered "John"  in cell A1, B2 of sheet1, and in cell B1 of sheet2. The purpose is to add these three cells to a custom collection and display a message indicating the count in each sheet (2 for sheet1 and 1 for sheet2).

I wrote following macro. It seems to be working fine on all fronts except in the "For Each sht" loop. User selects range (e.g. A1:B2) in the inputbox. I then loop through each sheet and search "John" in this range. However instead of searching in range "A1:B2" in each sheet, the macro selects this range for the active worksheet only. e.g. If sheet1 is active, then even though the "For each" loop selects next sheet, the search is done in the active sheet only. I am not sure how to make macro to select range ("A1:B2") for each worksheet. The statements are highlighted in bold/underline in the macro below.

Any help is highly appreciated.

Macro:

'----------------------------------------------------------

Sub GetCellContents()

    Dim sht As Worksheet

    Dim colCellContents As New Collection

    Dim cell As Range

    Dim i As Integer

    Dim t As Integer

    Dim str1 As Range

    Dim str2 As String

    Dim myRange As Range

    Dim adr As String

    Dim sname As String

    Set str1 = Application.InputBox("Select range to be searched in each sheet:", Type:=8)

    str2 = InputBox("Enter text:")

    For Each sht In ActiveWorkbook.Worksheets

        Set myRange = str1

        sname = sht.Name

        i = 0

        For Each cell In myRange

            If cell.Formula = str2 Then  '(Though the loop selects next sheet, this statement searches in the previous sheet range only)

                If colCellContents.Count = 0 Then

                    colCellContents.Add Item:=cell, Key:="first"

                Else

                    colCellContents.Add Item:=cell, before:=1

                End If

                i = i + 1

            End If

        Next cell

        t = t + i

        MsgBox "Total valid cells in " & sht.Name & ": " & i

    Next sht

    MsgBox "Total valid cells in workbook: " & t

End Sub

'-----------------------------------------------------------------------------------------------

Thank You

Regards,

Pawan

Answer 1

Try the following modification:

Dim str3 as String

Set str1 = Application.InputBox("Select range to be searched in each sheet:", Type:=8)

str3 = str1.AddressLocal

(Now, at the beginning of your For Each sht loop...)

Set myRange = sht.Range(str3)

HTH,

Eric

Answer 2

You don't really need the "Local" part.  I was in a hurry and added it without thinking!  :)

Eric

Answer 3

My mistake. I declared str3 as range rather than string. It works now.

Thanks for the quick response.

What does AddressLocal property do? Help says that it "Returns the range reference for the specified range in the language of the user". Does this mean that it activates the range for the selected object?

Answer 4

Thanks

I am getting 'Run time error 91: Object variable or With block variable not set' on the following line:

str3 = str1.AddressLocal