本帖最后由 wtzlsn 于 2017-5-22 15:03 编辑
效果如截图:根据B列C列D列已有的信息,从“台账”查找到绿色框内显示的相应信息。
Sub aaa()
Dim y, ar, i&, y2, br, j&
y = Sheets("食材付款单").Range("b65536").End(xlUp).Row
ar = Sheets("食材付款单").Range("a7:j" & y)
y2 = Sheets("台账").Range("b65536").End(xlUp).Row
br = Sheets("台账").Range("a5:m" & y2)
For i = 1 To UBound(ar)
For j = 1 To UBound(br)
If ar(i, 2) = br(j, 2) And ar(i, 3) = br(j, 3) And ar(i, 4) = br(j, 5) Then
ar(i, 5) = br(j, 8)
ar(i, 6) = br(j, 9)
ar(i, 7) = br(j, 10)
ar(i, 8) = br(j, 11)
ar(i, 9) = br(j, 7)
ar(i, 10) = br(j, 6)
Exit For
End If
Next j
Next i
Sheets("食材付款单").Range("a7").Resize(UBound(ar), 10) = ar
End Sub
本帖最后由 france723 于 2017-5-18 15:54 编辑
工作表名字自己修改, 我用的是01,02
- Sub aaa()
- Dim y, ar, i&, y2, br, j&
- y = Sheets("01").Range("b65536").End(xlUp).Row
- ar = Sheets("01").Range("a7:j" & y)
- y2 = Sheets("02").Range("b65536").End(xlUp).Row
- br = Sheets("02").Range("a5:m" & y2)
- For i = 1 To UBound(ar)
- For j = 1 To UBound(br)
- If ar(i, 2) = br(j, 2) And ar(i, 3) = br(j, 3) And ar(i, 4) = br(j, 5) Then
- ar(i, 5) = br(j, 8)
- ar(i, 6) = br(j, 9)
- ar(i, 7) = br(j, 10)
- ar(i, 8) = br(j, 11)
- ar(i, 9) = br(j, 7)
- ar(i, 10) = br(j, 6)
- Exit For
- End If
- Next j
- Next i
- Sheets("01").Range("a7").Resize(UBound(ar), 10) = ar
- End Sub
复制代码
|