EXCEL VBA Debug: Searching through the whole workbook

Question

I'm working on a VBA Macro for a database I have in Excel. I've got one Worksheet that stores information such as names, emails etc. (sadly those are not consistently placed in the same columns across all worksheets, but the email adresses span from "B:F"), this database is split into multiple worksheets. Except all those worksheets, I have also got one other worksheet ("Sheet2" in the code below) that stores all the email addresses that have assigned to my newsletter. (The only information in this sheet are the email addresses in the "A" column).

The VBA I'm working on should loop through all the email adresses that have subscribed to the newsletter ("Sheet2") and check if they're stored in "the database" - in the other sheets as well. If not, then give a warning - write "NOTFOUND" in the cell next to the email.

For some reason, VBA gives me a run-time error "Object doesn't support this property or method" on the row:

With Sheets(sheetIndex).Range("B:F").

Originally I thought that the reason for that is that I have not activated the Sheets, but I'm still getting the error.

The code I came up with so far:

Sub Search_for_emails()

Dim scanstring As String
Dim foundscan As Range
Dim lastRowIndex As Long
Dim ASheet As Worksheet

Set ASheet = Sheets("Sheet2")

lastRowInteger = ASheet.Range("A1", ASheet.Range("A1").End(xlDown)).Rows.Count

For rowNum = 1 To lastRowInteger
    scanstring = Sheets("Sheet2").Cells(rowNum, 1).Value
    For sheetIndex = 1 To ThisWorkbook.Sheets.Count
        Sheets(sheetIndex).Activate
        If Sheets(sheetIndex).Name <> "Sheet2" Then
            With Sheets(sheetIndex).Range("B:F")
                Set foundscan = .Find(What:=scanstring, LookIn:=xlValues, LookAt:=xlWhole, _
                    SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                    MatchCase:=False, SearchFormat:=False)
            End With
            If foundscan Is Nothing Then
                ASheet.Cells(rowNum, 2).Value = "NOTFOUND"

            Else

                ' ASheet.Cells(rowNum, 2).Value = foundscan.Rows.Count

            End If
        End If
    Next
Next rowNum

End Sub

FunThomas · Accepted Answer · 2018-08-22T07:23:18.153

Some points:

You should avoid Activate - no need for that.
You should always qualify things like sheet or range, else Excel will use the active workbook / sheet, and that is not always what you want.
There is a difference between the Sheets and the Worksheets collection. A Chart-sheet, for example, has no cells and therefore no Range.
You are declaring a variable lastRowIndex but uses lastRowInteger. To avoid such errors, always put Option Explicit at the top of your code.

Change your Sub to

Sub Search_for_emails()

    Dim scanstring As String
    Dim foundscan As Range
    Dim lastRowIndex As Long, rowNum As Long
    Dim ASheet As Worksheet

    Set ASheet = ThisWorkbook.Worksheets("Sheet2")
    lastRowIndex = ASheet.Range("A1", ASheet.Range("A1").End(xlDown)).Rows.Count

    For rowNum = 1 To lastRowIndex
        Dim ws As Worksheet
        For Each ws In ThisWorkbook.Worksheets
            If ws.Name <> "Sheet2" Then
                With ws.Range("B:F")
                    Set foundscan = .Find(What:=scanstring, LookIn:=xlValues, LookAt:=xlWhole, _
                        SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                        MatchCase:=False, SearchFormat:=False)
                End With
                If foundscan Is Nothing Then
                    ASheet.Cells(rowNum, 2).Value = "NOTFOUND"
                Else
                    ' ASheet.Cells(rowNum, 2).Value = foundscan.Rows.Count

                End If
            End If
        Next
    Next rowNum
End Sub

+1, nice explaination! If I could make a suggestion there should be an escape when `not foundscan is nothing` to avoid an unwanted "NOTFOUND". The search should go to `Next rowNum`. I just assume that is overlooked by OP here. — JvdV, Aug 21 '18 at 09:03

EXCEL VBA Debug: Searching through the whole workbook

1 Answers1