|
楼主 |
发表于 2019-10-4 16:38
|
显示全部楼层
Private Sub CommandButton1_Click()
Dim x
Set d = CreateObject("scripting.dictionary")
f = Dir(ThisWorkbook.Path & "\价格表.xls*")
If f = "" Then MsgBox "价格表不存在": Exit Sub
Set wb = Workbooks.Open(ThisWorkbook.Path & "\" & f)
Myr = wb.Worksheets(1).Cells(wb.Worksheets(1).Rows.Count, 1).End(xlUp).Row
Myc = wb.Worksheets(1).[iv1].End(xlToLeft).Column
br = wb.Worksheets(1).Range("a1").Resize(Myr, Myc) 'br = wb.Worksheets(15).[a1].CurrentRegion
For i = 2 To UBound(br)
If Trim(br(i, 3)) <> "" Then
d(Trim(br(i, 3))) = br(i, 5)
End If
Next i
wb.Close False
Columns("f:f").Insert Shift:=xlToRight '插入f行
Range("f2:f3").Merge '合并单元格
Range("f2").Value = "价格"
ActiveSheet.Range("f4:f" & ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row) = Empty
ar = ActiveSheet.UsedRange
For i = 4 To UBound(ar)
If d.exists(Trim(ar(i, 3))) Then
ar(i, 6) = d(Trim(ar(i, 3)))
End If
Next i
ActiveSheet.UsedRange = ar
'f列是插入后带入数据的,m列=f列*H列
End Sub
试了很多种方法,都不对,求指点! |
|