Excel VBA VLookup失败 | VLookup失败

本文介绍了Excel VBA VLookup失败的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我不是任何方式的VBA专家，但足够了解基础知识。我正在一个脚本来帮助一个当地的动物庇护所。有四张表已经手动合并成一份报告。所有四个报告都包含一个共同的变量，我已经命名为AnimalID。意图是在狂犬病更新表上找到AnimalID，并将到期日期返回到报告页（按Count增加的209个实例）。我已经将代码复制到下面的其中一个选择。当我尝试运行脚本时，我收到以下错误（通过msgbox Err.Description）：

AnimalID位于狂犬病更新表的第一列和报告的第Q列。狂犬病更新表上的K栏包含到期日，应复制到报告的第P列。我试图将AnimalID格式化为文本和数字，但没有成功。

  Dim Count As Long 
 Dim AnimalID，Renewal As Range 
 
工作表（报告）。激活
范围（Q：Q）。NumberFormat =@
工作表（狂犬病更新 ）。激活
范围（A：A）。NumberFormat =@
 
工作表（狂犬病更新）。激活
设置更新=表（狂犬病更新）.Range（A：AO）
 
工作表（报告）。激活
 
范围（Q8）。选择
范围（ActiveCell ，ActiveCell.End（xlDown））。选择
 Count = Selection.Count 
 
对于Count = 1计数
 AnimalID =Q& Count + 7 
 
范围（P& Count + 7）。选择
 ActiveCell.Value = WorksheetFunction.VLookup（AnimalID，Renewal，11，False）
下一个计数

我还尝试了一些语法变体，包括查找值为Range（AnimalID）。值;将函数定义为Application.WorksheetFunction.VLookup;表数组作为范围;和观点是真假。任何建议将不胜感激。谢谢你的帮助！

解决方案

你需要 Se ta 范围对象，而不仅仅是连接一个看起来像单元格的字符串地址。但是，您可以使用该级联的字符串来帮助定义Range对象。

  Set AnimalID = Range（Q& Count + 7）

这将设置 AnimalID 第一次通过，单元格随着每个后续通行进一步下降。

这是更广泛地重写您的程序，消除了使用的必要性。选择和。激活命令¹。

  Dim cnt As Long 
 Dim AnimalID As Range，Renewal As Range'< ~~声明这些正确！ 
 
与工作表（狂犬病更新）
 .Range（A：A）。NumberFormat =@
设置更新= .Range（A：AO ）
如果
 
与工作表（报告）
 .Range（Q：Q）。NumberFormat =@
 
对于cnt = 8到.Cells（Rows.Count，Q）。End（xlUp）.Row 
 Set AnimalID = .Range（Q& cnt）
 .Range（P& ; cnt）= _ 
 WorksheetFunction.VLookup（AnimalID，Renewal，11，False）
下一个计数
结束

I'm not a VBA expert by any means, but know enough to do the basics. I'm working on a script to help out a local animal shelter. There are four sheets that they've been manually combining into one report. All four reports contain a common variable, which I've named AnimalID. The intent is to find the AnimalID on the Rabies Renewal sheet and return an expiration date to the Report sheet (209 instances as incremented by Count). I've copied the code for one of the selections below. When I attempt to run the script, I receive the following error (through msgbox Err.Description):

AnimalID's are in the first column of the Rabies Renewal sheet and in column Q of the Report. Column K on the Rabies Renewal sheet contains expiration dates, which should copy to column P of the Report. I've tried formatting the AnimalID's as text and numbers but haven't had success.

Dim Count As Long
Dim AnimalID, Renewal As Range

Worksheets("Report").Activate
Range("Q:Q").NumberFormat = "@"
Worksheets("Rabies Renewal").Activate
Range("A:A").NumberFormat = "@"

Worksheets("Rabies Renewal").Activate
Set Renewal = Sheets("Rabies Renewal").Range("A:AO")

Worksheets("Report").Activate

Range("Q8").Select
Range(ActiveCell, ActiveCell.End(xlDown)).Select
Count = Selection.Count

For Count = 1 To Count
    AnimalID = "Q" & Count + 7

    Range("P" & Count + 7).Select
    ActiveCell.Value = WorksheetFunction.VLookup(AnimalID, Renewal, 11, False)
Next Count

I've also tried a few variants on the syntax, including the lookup value as Range(AnimalID).Value; defining the function as Application.WorksheetFunction.VLookup; table array as a range; and rangelookup as both true and false. Any suggestions would be greatly appreciated. Thank you for your help!

解决方案

You need to Set a Range object, not just concatenate a string that looks like the cell address. However, you can use that concatenated string to help define the Range object.

Set AnimalID = Range("Q" & Count + 7)

That will set AnimalID to the Q8 cell of the Report worksheet on the first pass of the For ...Next Statement and cells further down with each subsequent pass.

Here is a wider rewrite of your procedure, removing the necessity to use the .Select and .Activate commands¹.

Dim cnt As Long
Dim AnimalID As Range, Renewal As Range  '<~~declare these PROPERLY!

With Worksheets("Rabies Renewal")
    .Range("A:A").NumberFormat = "@"
    Set Renewal = .Range("A:AO")
End If

With Worksheets("Report")
    .Range("Q:Q").NumberFormat = "@"

    For cnt = 8 To .Cells(Rows.Count, "Q").End(xlUp).Row
        Set AnimalID = .Range("Q" & cnt)
        .Range("P" & cnt) = _
            WorksheetFunction.VLookup(AnimalID, Renewal, 11, False)
    Next Count
End With

这篇关于Excel VBA VLookup失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！