Excel VBA 宏：从一个工作表中查找/匹配单元格到另一个工作表中的列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26108467/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel VBA Macros: Find/Match cells from one sheet to a column in another
提问by Jongscx
Ok, so I have a workbook with multiple sheets. The Worksheets are named:
好的，所以我有一个包含多张工作表的工作簿。工作表被命名为：
Inputs Outputs Hardware
输入输出硬件
Input and output are serial IDs matched to actualy IP Addresses.
Input 1 : 192.168.0.1
input 2 : 192.168.0.2
... etc
输入和输出是与实际 IP 地址匹配的序列 ID。
输入 1：192.168.0.1 输入 2：192.168.0.2 ...等
Hardware has 3 columns. The first has Devices, 2nd column which has the Input Serial IDs and the 3rd of Output Serial IDs.
硬件有 3 列。第一个有设备，第二列有输入序列 ID 和第三列输出序列 ID。
Toaster : Input 1 : Output 3 Blender : Input 2 : Output 2 ...etc
烤面包机：输入 1：输出 3 搅拌机：输入 2：输出 2 ...等
Now, normally, I'd be using Vlookup(A1,Inputs!A:B,2) and Vlookup(A1,Outputs!A:B,2), but I have to incorporate this into the VBA macro we have and I have no idea how.
现在，通常，我会使用 Vlookup(A1,Inputs!A:B,2) 和 Vlookup(A1,Outputs!A:B,2)，但我必须将其合并到我们拥有的 VBA 宏中，我有不知道如何。
Sub TrackHardware()
'~~~~~~~~~~~~~~~~~~~~~
'Activating Device
'~~~~~~~~~~~~~~~~~~~~~
currentOutputRow = 2
Dim test As String
For currentRow = 2 To 32768 'The last row of your data
'For Loop to go through contents of Hardware individually
If Not (IsEmpty(Worksheets("Hardware").Range("A" & currentRow).Value)) Then
'To Skip the empty cells
HWID=Worksheets("Hardware").Range("a" & currentvalue).Value
'HWID is the search term coming from Sheet:'Hardware'
Desc=Worksheets("Hardware").Range("D" & currentvalue).Value
'Desc is the Plain Text description coming from Sheet:'Hardware'
inputrow={Match pseudocode that didn't work(HWID, "Inputs", Range:= "A:B", 2) }
outputrow={Match pseudocode that didn't work(HWID, "Outputs", Range:= "A:B", 2) }
'trying to find the row # of search term in Sheets 'Input' and 'Output'
Worksheets("Inputs").Range("C" & inputrow).Value = Desc
Worksheets("Outputs").Range("C" & outputrow).Value = Desc
'Pastes The Device Description to Input and Output Sheets
End If
Next currentRow
'And on to the next line in 'Hardware'
End Sub
I'd also like to account for Errors like 2 devices on the same Input/Output or a Blank cell, but I think I can figure those out myself. This Find function is what's really giving me a lot of trouble.
我还想考虑同一输入/输出上的 2 个设备或空白单元格等错误，但我想我可以自己解决这些问题。这个 Find 功能真的给我带来了很多麻烦。
采纳答案by David Zemens
First, there seems to be a problem if you are not able to call on the Application.Matchfunction. I am not sure why that would be missing, but I know there are some "limited" versions of Office/Excel which do not have full VBA functionality. I am not sure if that is the case with your installation.
首先，如果您无法调用该Application.Match函数，则似乎存在问题。我不确定为什么会丢失，但我知道有一些“有限”版本的 Office/Excel 没有完整的 VBA 功能。我不确定您的安装是否是这种情况。
Now, on to your problem though...
现在，尽管解决您的问题...
To use the Application.Matchfunction:
要使用该Application.Match功能：
The Matchfunction takes a single row or single column range input. You are attempting to pass in the range "A:B", which will always raise an error. Change to a single row/column range instead.
该Match函数采用单行或单列范围输入。您正在尝试传入 range "A:B"，这将始终引发错误。改为改为单行/列范围。
Further, 2is not an option for the third argument, which can be either -1 (less than), 0 or False (exact), or 1 (greater than). I'm not sure this alone will raise an error, but you should fix it anyways.
此外，2不是第三个参数的选项，它可以是 -1（小于）、0 或 False（精确）或 1（大于）。我不确定仅此一项会引发错误，但无论如何您都应该修复它。
inputRow = Application.Match(HWID, Worksheets("Inputs").Range("A:A"), False)
If an exact match cannot be found, it will raise an error, which you can trap like so:
如果无法找到完全匹配，则会引发错误，您可以像这样捕获错误：
inputRow = Application.Match(HWID, Worksheets("Inputs").Range("A:A"), False)
If IsError(inputRow) Then
'Do something like:
MsgBox HWID & " not found!", vbInformation
Exit Sub
End If
NOTEIf you actually need to check bothcolumns, then you can either double up on the Matchfunction, or use the range .Findmethod instead.
注意如果您确实需要检查两列，那么您可以在Match函数上加倍，或者改用范围.Find方法。
Dim foundRange as Range
Set foundRange = Range("A:B").Find(HWID)
If Not foundRange Is Nothing Then
inputRow = foundRange.Row
Else
MsgBox HWID & " not found!", vbInformation
End If
Handling errors with WorksheetFunction.Match
处理错误 WorksheetFunction.Match
Error trapping for Application.WorksheetFunction.Matchshould be something like:
错误捕获Application.WorksheetFunction.Match应该是这样的：
inputRow = Empty
On Error Resume Next
inputRow = Application.WorksheetFunction.Match(HWID, Worksheets("Inputs").Range("A:A"), False)
If Err.Number <> 0 Then
MsgBox "Match not found", vbInformation
Exit Sub
End If
On Error GoTo 0
