Vlookup型MisMatch

Question

我在下面的代码中使用了vlookup，但是它带来了类型不匹配。我已经更改了所有的常量，使变量变体，将其更改为应用程序，而不是工作表函数，但我仍然得到了错误。有人能发现我做错了什么吗？

Sub createSQL()

Dim Br As Range
Dim Branch As Variant
Dim Rep As Range
Dim Report As Variant
Dim RowNo As Long
Dim SQLCode As Range
Dim SQLCode2 As String
Dim SQLCode3 As String
Dim BranchID As Long
Dim Exception As String
Dim ExCode As Variant

Set Br = Sheets("sheet3").Range("D2:D5")
SQLCode3 = Sheets("Issues").Range("F2")
SQLCode2 = Sheets("Issues").Range("F3")

For Each Branch In Br

RowNo = Branch.Row
Set SQLCode = Sheets("Sheet3").Range("L" & RowNo)
BranchID = Sheets("Sheet3").Range("C" & RowNo)
SQLCode = SQLCode3 & BranchID & SQLCode2

Set Rep = Sheets("Sheet3").Range("I" & RowNo & ":K" & RowNo).Columns

For Each Report In Rep

If Report <> "" Then
SQLCode = SQLCode & Report
Else
SQLCode = ""

End If

ExCode = Sheets("Sheet3").Range("C" & RowNo) & Sheets("Sheet3").Range("D" & RowNo) & Cells(1, Report.Column)

Exception = Application.VLookup(ExCode, Sheets("Exceptions").Range("D2:E2"), 2, False)

SQLCode = SQLCode & Exception & " Union All "

Next Report
SQLCode = Left(SQLCode, Len(SQLCode) - 10)

Next Branch

End Sub

Answer 1

第一个问题是它应该是Application.WorksheetFunction.VLookup。

第二个问题是，VLookUp可能返回一个错误，这不会进入任何字符串，因此您必须在将它赋值给Exception之前对其进行测试。

我只是倾斜了一下，但是你试图在上做一个垂直查找，上只有一行.这无疑是错误的根源，因为你不会在一排中找到很多不同的东西.

如果将ExCode和Exception设置为String，则它可能与以下代码一起工作：

Dim LookUp_Range As Range
Set LookUp_Range = Sheets("Exceptions").Range("D2:E10")


If IsError(Application.WorksheetFunction.VLookup(ExCode, LookUp_Range, 2, False)) Then
    Exception = "Error"
Else
    Exception = Application.WorksheetFunction.VLookup(ExCode, LookUp_Range, 2, False)
End If

所以您的整个代码将如下所示：

Sub createSQL()

Dim Br As Range
Dim Branch As Variant
Dim Rep As Range
Dim Report As Variant
Dim RowNo As Long
Dim SQLCode As Range
Dim SQLCode2 As String
Dim SQLCode3 As String
Dim BranchID As Long
Dim Exception As String
Dim ExCode As String
Dim LookUp_Range As Range

Set Br = Sheets("sheet3").Range("D2:D5")
SQLCode3 = Sheets("Issues").Range("F2")
SQLCode2 = Sheets("Issues").Range("F3")

Set LookUp_Range = Sheets("Exceptions").Range("D2:E10")

With Sheets("Sheet3")
    For Each Branch In Br
        RowNo = Branch.Row
        Set SQLCode = .Range("L" & RowNo)
        BranchID = .Range("C" & RowNo)
        SQLCode = SQLCode3 & BranchID & SQLCode2
        Set Rep = .Range("I" & RowNo & ":K" & RowNo).Columns
        For Each Report In Rep
            If Report <> "" Then
                SQLCode = SQLCode & Report
            Else
                SQLCode = ""
            End If
            ExCode = .Range("C" & RowNo) & .Range("D" & RowNo) & Cells(1, Report.Column)
            If IsError(Application.WorksheetFunction.VLookup(ExCode, LookUp_Range, 2, False)) Then
                Exception = "Error"
            Else
                Exception = Application.WorksheetFunction.VLookup(ExCode, LookUp_Range, 2, False)
                'Concatenate only if there is a match!
                SQLCode = SQLCode & Exception & " Union All "
            End If
        Next Report
        SQLCode = Left(SQLCode, Len(SQLCode) - 10)
    Next Branch
End With
End Sub