给你一段大概的代码,由于没有数据,无法测试,你调试下: Sub Test() Dim Sht1 As Worksheet, Sht2 As Worksheet, w1 As String, i As Long Dim iDic As New Dictionary, iNum As Long, iC As Long Dim Rng As Range Set Sht1 = Worksheets("销售单") Set Sht2 = Worksheets("销售明细") iNum = CLng(Sht1.Range("e4").Value) ''销售单号 ''用字典记录,好处是不怕错位 iDic.CompareMode = TextCompare With Sht1 For i = 6 To .Cells.Rows.Count w1 = Trim(.Cells(i, 4)) If w1 = "" Then Exit For ''如果商品名称为空则退出 iDic(w1) = .Cells(i, 5) Next End With ''开始找对应的单号 Set Rng = Sht2.Range("d1:d" & Sht2.Cells.Columns.Count).Find(what:=CStr(iNum), lookat:=xlWhole) If Rng Is Nothing Then MsgBox "没找到对应的单号!" Exit Sub End If iC = Rng.Column ''目标列 ''开始填充 With Sht2 For i = 3 To .Cells.Rows.Count w1 = Trim(.Cells(i, 3)) If w1 = "" Then Exit For ''如果商品名称为空则退出 If iDic.Exists(w1) Then .Cells(i, iC) = iDic(w1) Next End With End Sub
Sub Test()
Dim Sht1 As Worksheet, Sht2 As Worksheet, w1 As String, i As Long
Dim iDic As New Dictionary, iNum As Long, iC As Long
Dim Rng As Range
Set Sht1 = Worksheets("销售单")
Set Sht2 = Worksheets("销售明细")
iNum = CLng(Sht1.Range("e4").Value) ''销售单号
''用字典记录,好处是不怕错位
iDic.CompareMode = TextCompare
With Sht1
For i = 6 To .Cells.Rows.Count
w1 = Trim(.Cells(i, 4))
If w1 = "" Then Exit For ''如果商品名称为空则退出
iDic(w1) = .Cells(i, 5)
Next
End With
''开始找对应的单号
Set Rng = Sht2.Range("d1:d" & Sht2.Cells.Columns.Count).Find(what:=CStr(iNum), lookat:=xlWhole)
If Rng Is Nothing Then
MsgBox "没找到对应的单号!"
Exit Sub
End If
iC = Rng.Column ''目标列
''开始填充
With Sht2
For i = 3 To .Cells.Rows.Count
w1 = Trim(.Cells(i, 3))
If w1 = "" Then Exit For ''如果商品名称为空则退出
If iDic.Exists(w1) Then .Cells(i, iC) = iDic(w1)
Next
End With
End Sub