多工作表间查找,请帮忙?
如附件中文件,想通过第一个工作表中的ITEM列,找出在其它工作表中在Part#列中出现的行!
如:工作表1的ITEM为AA, 找到工作表2中有AA的,打上标记就可以了。
Book1.rar (1.49 KB)
Sub FoundItem()
Dim vCodeNum() As Variant
Dim lSourceRow As Long
Dim lSheet2ALastRow As Long
Dim bTag As Boolean
Dim i As Long, j As Long
lSourceRow = Sheet7.Range("B65536").End(xlUp).Row '取得这工作表最后一行有数据行号。
lSheet2ALastRow = Sheet1.Range("D65536").End(xlUp).Row
ReDim vCodeNum(1 To lSourceRow - 1)
For i = 2 To lSourceRow
vCodeNum(i - 1) = Sheet7.Range("B" & i) '把要查找的值传给数组
Next
For i = 2 To lSheet2ALastRow
bTag = True
For j = 1 To lSourceRow - 1
If Sheet1.Range("D" & i) = vCodeNum(j) Then
Sheet1.Range("D" & i).Offset(0, -3).Value = "B" & j + 1
bTag = False
End If
Next
If bTag Then
Sheet1.Range("D" & i).Offset(0, 5).Value = "无"
End If
Next
End Sub
找到一段以上代码应该可以。但是我的工作表数是好几个想把这一段中的被查找工作表用变量代入(sheet1用变量),应该要知道两个相关属性,一个是工作表数,还有一个是工作表名。
For i = 2 To lSheet2ALastRow
bTag = True
For j = 1 To lSourceRow - 1
If Sheet1.Range("D" & i) = vCodeNum(j) Then
Sheet1.Range("D" & i).Offset(0, -3).Value = "B" & j + 1
bTag = False
End If
Next
If bTag Then
Sheet1.Range("D" & i).Offset(0, 5).Value = "无"
End If
Next
多工作表间查找,请帮忙?
如附件中文件,想通过第一个工作表中的ITEM列,找出在其它工作表中在Part#列中出现的行!
如:工作表1的ITEM为AA, 找到工作表2中有AA的,打上标记就可以了。
Book1.rar (1.49 KB)
Sub FoundItem()
Dim vCodeNum() As Variant
Dim lSourceRow As Long
Dim lSheet2ALastRow As Long
Dim bTag As Boolean
Dim i As Long, j As Long
lSourceRow = Sheet7.Range("B65536").End(xlUp).Row '取得这工作表最后一行有数据行号。
lSheet2ALastRow = Sheet1.Range("D65536").End(xlUp).Row
ReDim vCodeNum(1 To lSourceRow - 1)
For i = 2 To lSourceRow
vCodeNum(i - 1) = Sheet7.Range("B" & i) '把要查找的值传给数组
Next
For i = 2 To lSheet2ALastRow
bTag = True
For j = 1 To lSourceRow - 1
If Sheet1.Range("D" & i) = vCodeNum(j) Then
Sheet1.Range("D" & i).Offset(0, -3).Value = "B" & j + 1
bTag = False
End If
Next
If bTag Then
Sheet1.Range("D" & i).Offset(0, 5).Value = "无"
End If
Next
End Sub
找到一段以上代码应该可以。但是我的工作表数是好几个想把这一段中的被查找工作表用变量代入(sheet1用变量),应该要知道两个相关属性,一个是工作表数,还有一个是工作表名。
For i = 2 To lSheet2ALastRow
bTag = True
For j = 1 To lSourceRow - 1
If Sheet1.Range("D" & i) = vCodeNum(j) Then
Sheet1.Range("D" & i).Offset(0, -3).Value = "B" & j + 1
bTag = False
End If
Next
If bTag Then
Sheet1.Range("D" & i).Offset(0, 5).Value = "无"
End If
Next