☰

运行时错误 1004 - vlookup

tag-icon • tag-icon microsoft-excel vba
运行时错误 1004 - vlookup

这是我的代码：

Sub index_test()
Dim i As Double
Dim Newsht As Worksheet
Dim countnonblank1, countnonblank2 As Integer
Dim myRange, nuRange As Range
Dim nuClass As Variant
Dim inpt, Msg, Title, MyInput As String


Set Newsht = ThisWorkbook.Sheets.Add(After:= _
             ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
    Newsht.Name = "new_temp"

Sheets("INDEX").Columns("a:b").Copy Sheets("new_temp").Columns("b:c")

Sheets("data test").Columns("e").Copy Sheets("new_temp").Columns("a")

 Columns("A:A").Select
 ActiveSheet.Range("$A$1:$A$500").RemoveDuplicates Columns:=1, Header:=xlNo

Set myRange = Columns("A:A")
countnonblank1 = Application.WorksheetFunction.CountA(myRange)
Set nuRange = Columns("B:B")
countnonblank2 = Application.WorksheetFunction.CountA(myRange)


For i = 2 To countnonblank1 + 1
**If Application.WorksheetFunction.VLookup**(Cells(i, 1), Range(Cells(2, 2), Cells(countnonblank2 + 1, 2)), 1, 0) = "#N/A" Then
inpt = Cells(i, 1)

 Msg = "choose a new Index from the list for" & inpt & "? " _
    & vbNewLine & "äëðñåú" & vbNewLine _
    & "äðäìä" & vbNewLine _
    & "îç÷ø" & vbNewLine _
    & "ôéúåç" & vbNewLine _
    & "ùéåå÷" & vbNewLine _
    & "ñééáø"

    Title = "Selection of Index" ' Define title.
    MyInput = InputBox(Msg, Title)
    Select Case MyInput
    Case "äëðñåú", "äðäìä", "îç÷ø", "ôéúåç", "ùéåå÷", "ñééáø"

       Cells(i, 3) = MyInput
       Case Else
        MsgBox "not ok"


    End Select

Else

End If

Next

End Sub

在 vlookup 应用程序中我收到运行时错误 1004，有人知道为什么以及如何修复它吗？

提前谢谢

答案1

countnonblank2 = Application.WorksheetFunction.CountA(myRange)

应该

countnonblank2 = Application.WorksheetFunction.CountA(nuRange)

因为您正在计算 B 列中的非空白单元格，并在VLOOKUP函数中使用它。

编辑：
您的代码中还有更多错误。myRange不是声明为范围而是声明为变体！ 类似这样的声明Dim a,b,c as range仅定义c为范围，而a将和b定义为变体。 正确的语法应该是Dim a as range, b as range, c as range。
您的代码中countnonblank1, myRange,inpt, Msg, Title所有代码都需要编辑。

相关内容