|
我把代码全部更新一遍,后面还有说明:
Application.ScreenUpdating = False
Dim arr1(100, 2)
js = 0
For i = 60 To Worksheets("产品结构表").Range("h80").End(3).Row
js = js + 1
arr1(js, 1) = Worksheets("产品结构表").Cells(i, "h")
arr1(js, 2) = Worksheets("产品结构表").Cells(i, "i")
Next i
Dim arr
arr = Worksheets("产品结构表").Range("a1:x" & Worksheets("产品结构表").[d10000].End(3).Row)
For i = 2 To [b10000].End(3).Row
For k = 2 To UBound(arr)
If (UCase(Cells(i, 6)) = UCase(arr(k, 13))) And IsEmpty(Cells(i, "i")) Then
Cells(i, "i") = arr(k, 14)
End If
If (UCase(Cells(i, 4)) = UCase(arr(k, 1))) And IsEmpty(Cells(i, "j")) Then
Cells(i, "j") = arr(k, 2)
End If
If (UCase(Cells(i, 5)) = UCase(arr(k, 4))) And IsEmpty(Cells(i, "k")) Then
Cells(i, "k") = arr(k, 5)
Cells(i, "p") = arr(k, 6)
End If
If (UCase(Cells(i, 4)) = UCase(arr(k, 16))) And IsEmpty(Cells(i, "l")) Then
Cells(i, "l") = arr(k, 17)
Cells(i, "m") = arr(k, 18)
End If
Next k
If Not IsEmpty(Cells(i, "f")) Then
bz = False
For j = 1 To js
If UCase(Cells(i, "f")) = UCase(arr1(j, 1)) Then
bz = True
jl = j
Exit For
End If
Next j
If bz Then
Cells(i, "n") = arr1(jl, 2) & Cells(i, "l")
Else
Cells(i, "n") = Cells(i, "f") & Cells(i, "l")
End If
End If
If Not IsEmpty(Cells(i, "i")) Then
Cells(i, "o") = IIf(IsEmpty(Cells(i, "o")), Cells(i, "i") & Cells(i, "m"), Cells(i, "o"))
End If
If Not IsEmpty(Cells(i, "j")) Then
Cells(i, "q") = IIf(IsEmpty(Cells(i, "q")), Cells(i, "p") & Cells(i, "j"), Cells(i, "q"))
End If
Next i
Application.ScreenUpdating = True
说明:
1、只有空白的单元格才会更新数据:
If (UCase(Cells(i, 6)) = UCase(arr(k, 13))) And IsEmpty(Cells(i, "i")) Then
IsEmpty(Cells(i, "i"))表示此单元格是空白,后面的条件都类似
2、下列两种情况下:
源数据做了大幅度更新;
手工更新极少;
可以通过人工删除J-O列的全部或部分内容来达到重新更新的目的,代码并不能区别哪些是人工更正的单元格,只要是空白的都会尝试去更新,因此你可以通过手工删除来控制空白单元格达到重新更新的目的。简单说就是你想更新哪个部分就把要更新的区域删掉就可以了;
|
|