初學不才..此VBA如何簡化?感謝!?

2016-06-01 7:12 am

各位老師好:

以下是我跨檔案依指定儲存格值而取的另一檔案儲存格值所寫，但是.........我要從F4......G4......H4.....一直做到Z4，RANGE()的變動不會寫，只能一直COPY做.....:'(

還請老師指導....感謝!

Public Sub test()
Range("f4:f9") = "='D:\Desktop\a\[" & [F2] & ".xlsm]b'!" & [$d4] & ""
Range("f10:f14") = "='D:\Desktop\a\[" & [F2] & ".xlsm]b'!" & [$d10] & ""
Range("f15:f20") = "='D:\Desktop\a\[" & [F2] & ".xlsm]b'!" & [$d15] & ""
Range("g4:g9") = "='D:\Desktop\a\[" & [G2] & ".xlsm]b'!" & [$d4] & ""
Range("g10:g14") = "='D:\Desktop\a\[" & [G2] & ".xlsm]b'!" & [$d10] & ""
Range("g15:g20") = "='D:\Desktop\a\[" & [G2] & ".xlsm]b'!" & [$d15] & ""

end sub

更新1:

excel跨檔案指定儲存格取取儲存格值vba....已寫求簡化!

回答 (2)

Cheng

2016-06-03 2:36 pm

✔ 最佳答案

Sub xx()
For i = 6 To 26
Range(Cells(4, i), Cells(9, i)) = i
Range(Cells(10, i), Cells(14, i)) = i + 2
Range(Cells(15, i), Cells(20, i)) = i + 4
Next
End Sub

Sub dd()
For i = 6 To 26
Cells(4, i).Resize(6, 1) = i
Cells(10, i).Resize(5, 1) = i + 2
Cells(15, i).Resize(6, 1) = i + 4
Next
End Sub

👍 0 👎 0

Daniel

2016-06-06 5:27 am

.
Sub TT()
For i = 6 To 26
Cells(4, i).Resize(6) = "='D:\Desktop\a\[" & Cells(2, i) & ".xlsm]b'!" & [D4] & ""
Cells(10, i).Resize(5) = "='D:\Desktop\a\[" & Cells(2, i) & ".xlsm]b'!" & [D10] & ""
Cells(15, i).Resize(6) = "='D:\Desktop\a\[" & Cells(2, i) & ".xlsm]b'!" & [D15] & ""
Next
End Sub

👍 0 👎 0

收錄日期: 2021-05-03 13:46:03
原文連結 [永久失效]:
https://hk.answers.yahoo.com/question/index?qid=20160531231238AA8dRwo

檢視 Wayback Machine 備份