|
本帖最后由 lijian8003 于 2014-10-20 21:58 编辑
工作表“模版1”中:A列数据与B列数据一一对应,即A1对应B1,A2对应B2...
工作表“转换”中:若已知有A列-F列数据,如何用宏代码,根据模版1的数据对应关系,将A列-F列数据,转化为H列-M列数据?
上述例子是有限的行列,实际运用中有若干行列。
- Sub demo()
- Dim a As Long, ar, d As Object, b As Long, br
- a = Sheets("模版1").Cells(Rows.Count, 1).End(xlUp).Row
- ar = Sheets("模版1").Range("a1:b" & a)
- Set d = CreateObject("Scripting.Dictionary")
- For i = 1 To UBound(ar)
- d(ar(i, 1)) = "'" & ar(i, 2)
- Next
- b = Sheets("转换").Cells(Rows.Count, 1).End(xlUp).Row
- br = Sheets("转换").Range("a1:f" & b)
- For i = 1 To UBound(br)
- For j = 1 To UBound(br, 2)
- br(i, j) = d(br(i, j))
- Next
- Next
- Sheets("转换").Range("h1").Resize(UBound(br), UBound(br, 2)) = br
- End Sub
复制代码楼主试试
|
|