|
在一个excel表格中有两个工作薄在一个工作薄中数据在第二个表格里查找数据对应得其他数据,如果在第二表格里面没有这个数据,下面的宏就会报错
Sub 查找()
Dim P_N, r, l As String
Dim rng As Range
l = Worksheets("Sale BOM").Range("b65536").End(xlUp).Row
For r = 2 To l
P_N = Cells(r, 5).Value
with Worksheets("ExcelBOM").Range("D:D")
n = .Find(P_N).Row
End With
With Workbooks("輸入格式")
.Worksheets("Sale BOM").Cells(r, 6) = .Worksheets("ExcelBOM").Cells(n, 5).Value
End With
Next r
End Sub
Sub 查找()
Dim P_N, r, l As String
l = Worksheets("s_fine").Range("a65536").End(xlUp).Row
For r = 1 To l
' On Error Resume Next
P_N = Worksheets("s_fine").Cells(r, 1).Value
With Worksheets("data").Range("A:A")
n = .Find(P_N).Row
End With
o = Worksheets("data").Cells(n, 2).Value
Worksheets("s_fine").Cells(r, 2) = o
Next r
End Sub
橙色部分是查找操作,是不安因素,即可能出错。
比如,当r=4时,没查找到A1004对应位置,而On Error Resume Next屏蔽了错误,导致n沿用r=3时所找的对应位置(6)。
修改如下
Sub 查找2()
Dim P_N, r, l As String, rng, sh1, sh2
Set sh1 = Worksheets("s_fine")
Set sh2 = Worksheets("data")
sh1.Columns(2).ClearContents
l = sh1.Range("a65536").End(xlUp).Row
For r = 1 To l
P_N = sh1.Cells(r, 1).Value
Set rng = sh2.Range("A:A").Find(P_N)
'如果找的到(即没发生错误)
If Not rng Is Nothing Then
'才执行指定的(赋值)操作
n = rng.Row
o = sh2.Cells(n, 2).Value
sh1.Cells(r, 2) = o
End If
Next r
End Sub
|
|