第1个回答 2012-09-03
Sub test()
Dim rs As Range
Dim B As Long
Set fDic = CreateObject("Scripting.Dictionary")
iRow = Sheet2.Cells(1, 1).End(xlDown).Row
Arr = Sheet2.Cells(1, 1).Resize(iRow, 2)
For w = 1 To iRow
On Error Resume Next
m = Arr(w, 1)
fDic.Add m, 0
If Err.Number = 0 Then
fDic.Item(m) = Sheet2.Cells(w, 2)
Else
Err.Clear
On Error GoTo 0
fDic.Item(m) = fDic.Item(m) + Sheets2.Cells(w, 2)
End If
Next
For Each rs In Sheet1.UsedRange
B = Left(rs.Value, 1)
rs.Value = fDic.Item(B) & Right(rs.Value, 2)
Next
End Sub
这样就可以了 ,我觉得你还是把你表里的数据格式发出来点,否则很有可能不通用,要让别人一次次的改
第2个回答 2012-09-03
不用宏,用VLOOKUP即可
假设你的Sheet1里的数据在A列
Sheet2里的数据在A,B两列
具体公式如下
“=if(isna(vlookup(a1,sheet2!a:b,2,0)),a1,vlookup(a1,sheet2!a:b,2,0))”
第3个回答 2017-10-20
复制以下宏,有疑问再私信我。
Sub Macro3()
'
' Macro3 Macro
'
'
Sheets("sheet1").Select
Range("D1").Select
ActiveCell.FormulaR1C1 = _
"=INDEX(Sheet2!R1C2:R3C2,MATCH(MID(RC[-3],1,1)*1,Sheet2!R1C1:R3C1,0))&MID(RC[-3],2,2)"
Range("D1").Select
Selection.AutoFill Destination:=Range("D1:D3"), Type:=xlFillDefault
Range("D1:D3").Select
Range("D1").Select
Range("D1:D3").Select
Selection.Copy
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("D1:D3").Select
Selection.ClearContents
Range("D1").Select
ActiveWindow.SmallScroll Down:=-9
End Sub