VBA 列值比较
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7644388/
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
VBA column value comparison
提问by user899902
I have two excel sheets. The first sheet has this data:
我有两张excel表。第一张表有以下数据：
Column C
--------
101-AA-103
101-AA-104
101-AA-105
101-BB-101
The second sheet has this data:
第二张表有以下数据：
Column A
--------
101-AA-100
101-AA-101
101-AA-102
101-AA-103
I want to compare column C from the first sheet with column A from the second sheet. For example, value 101-AA-103 from column C on the first sheet needs to be checked against all the rows in column A on the second sheet. If the value is found, it should say "Available"; otherwise, "Not Available". How can I write a VBA function to do this?
我想将第一张表中的 C 列与第二张表中的 A 列进行比较。例如，第一张纸上 C 列的值 101-AA-103 需要对照第二张纸上 A 列中的所有行进行检查。如果找到该值，则应显示“可用”；否则，“不可用”。如何编写 VBA 函数来执行此操作？
回答by Marco
Try this:
尝试这个：
Dim sh1 As Worksheet
Dim sh2 As Worksheet
Dim strToFind As String
Dim res As Range
dim maxrows as Integer
Set sh1 = ThisWorkbook.Sheets("Sheet1")
Set sh2 = ThisWorkbook.Sheets("Sheet2")
maxrows = 500
For i = 1 To maxrows
strToFind = sh2.Cells(i, "A")
With sh1
Set res = .Columns("C").Find(What:=strToFind, After:=.Cells(1, "C"), LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If res Is Not Nothing Then
'Do here what you please
End If
End With
Next
Note that you should calculate maxrows and not use a const integer.
请注意，您应该计算 maxrows 而不是使用 const 整数。
回答by aevanko
You could do this really easily using Vlookup:
您可以使用 Vlookup 轻松完成此操作：
=IF(ISERROR(VLOOKUP(C1,Sheet2!A:A,1,FALSE)),"Not Available","Available")
But since you asked for VBA, here's a function that will do it, utilizing a dictionary object and variant arrays for efficiency and speed.
但是由于您要求使用 VBA，这里有一个函数可以做到这一点，它利用字典对象和变体数组来提高效率和速度。
- Dump Column C and Column A into a variant array
- Make a dictionary of Column A values
- Search through the column C entries to see if they exist in A
- The variable i is the row number as well, so it's rather simple to place text in column D.
- 将 C 列和 A 列转储到变体数组中
- 制作 A 列值的字典
- 搜索 C 列条目以查看它们是否存在于 A 中
- 变量 i 也是行号，因此在 D 列中放置文本相当简单。
Sub TestAvailability()
Application.ScreenUpdating = False
Dim varrayC As Variant, varrayA As Variant
Dim lastRow As Long
Dim dict As Object
Set dict = CreateObject("scripting.dictionary")
lastRow = Sheets(2).range("A" & Rows.count).End(xlUp).Row
varrayA = Sheets(2).range("A1:A" & lastRow).Value
lastRow = Sheets(1).range("C" & Rows.count).End(xlUp).Row
varrayC = Sheets(1).range("C1:C" & lastRow).Value
On Error Resume Next
For i = 1 To UBound(varrayA, 1)
dict.Add varrayA(i, 1), 1
Next
For i = 1 To UBound(varrayC, 1)
If dict.exists(varrayC(i, 1)) = True Then
Sheets(1).cells(i, 4).Value = "Available"
Else
Sheets(1).cells(i, 4).Value = "Not Available"
End If
Next
Application.ScreenUpdating = True
End Sub
Technically you could create a new array of availability and transpose it on column D, but I didn't want to make it overly complicated.
从技术上讲，您可以创建一个新的可用性数组并将其转置到 D 列，但我不想让它变得过于复杂。
