excel - 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

标签： excelvba

Answer 1

几点：

• 你应该避免Activate - 不需要。
• 您应该始终限定 sheetor之类的东西range，否则 Excel 将使用活动的工作簿/工作表，而这并不总是您想要的。
• Sheets和Worksheets集合之间是有区别的。Chart例如，一张-sheet 没有单元格，因此没有Range.
• 您正在声明一个变量lastRowIndex，但使用lastRowInteger. 为避免此类错误，请始终放在Option Explicit代码的顶部。

将您的子更改为

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