|
楼主 |
发表于 2020-9-28 14:28
|
显示全部楼层
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用 · 内置多项VBA编程加强工具 ★ 免费下载 ★ ★ 使用手册★
对于第一个导出数据的工具,如果其中的数据是读取自工作簿、xlsx,例如参数0-参数10,但是只需要将其中的参数0-参数3,参数6,参数8-参数10写入到工作簿.xlsx中对应的表格,原因是,工作簿中参数4、参数5、参数7中包含函数,如果反写就会清除函数。请指教如何修改语句?
Sub aaa()
Dim i%, j%, k%, dic, wb
Dim arr(), brr(), crr(), drr()
Dim farpath As String
Application.ScreenUpdating = False
Application.DisplayAlerts = False
farpath = Left(ThisWorkbook.Path, InStr(ThisWorkbook.Path, "\"))
arr = Sheets("Sheet1").Range("g4:h" & [g65536].End(xlUp).Row).Value
Set dic = CreateObject("scripting.dictionary")
farpath = "e:\"
For i = 2 To UBound(arr)
dic(arr(1, 2) & arr(i, 1)) = arr(i, 2)
Next i
crr = dic.keys: drr = dic.items
Set wb = Workbooks.Open(farpath & "工作簿.xlsx")
With wb.Sheets("sheet1").Range("a1:bm" & [a65536].End(xlUp).Row)
brr = .Value
End With
For k = 0 To UBound(crr)
For i = 2 To UBound(brr)
For j = 2 To UBound(brr, 2)
If brr(i, 1) & brr(1, j) = crr(k) Then
wb.Sheets("Sheet1").Cells(i, j) = drr(k)
End If
Next
Next
Next
Erase arr
Erase brr
Erase crr
Erase drr
wb.Save
wb.Close
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub
|
|