我正在嘗試構建一個作業表以從作業簿（帶有宏）檔案中收集和匯總資料。 l 需要的資料是從作業簿的一張表中的許多不同單元格中組合而成的，然后 l 將以“僅值格式”顯示結果。
你能幫我縮短代碼并讓它運行得更快嗎？我收集資料的作業簿是 Test.xlsm，我有大約 30 個專案。非常感謝
下面的代碼是我所做的。
Sub Test1()
'
' Test1 Macro
Dim Slaw150 As Variant
Dim Slaw200 As Variant
Dim Slaw300 As Variant
Dim Slaw400 As Variant
Slaw150 = "=SUM('[Test.xlsm]Test'!$CO$66:$CS$66,'[Test.xlsm]Test'!$CO$88:$CS$88,'[Test.xlsm]Test'!$CO$95:$CS$95)"
Slaw200 = "=SUM('[Test.xlsm]Test'!$CO$67:$CS$67,'[Test.xlsm]Test'!$CO$89:$CS$89,'[Test.xlsm]Test'!$CO$96:$CS$96)"
Slaw300 = "=SUM('[Test.xlsm]Test'!$CO$68:$CS$68,'[Test.xlsm]Test'!$CO$90:$CS$90,'[Test.xlsm]Test'!$CO$97:$CS$97)"
Slaw400 = "=SUM('[Test.xlsm]Test'!$CO$69:$CS$69,'[Test.xlsm]Test'!$CO$91:$CS$91,'[Test.xlsm]Test'!$CO$98:$CS$98)"
With Range("C42")
.Value = Slaw150
.Value = .Value
End With
With Range("C43")
.Value = Slaw200
.Value = .Value
End With
With Range("C44")
.Value = Slaw300
.Value = .Value
End With
End Sub
uj5u.com熱心網友回復：
請測驗下一個方法：
Sub TestSUMM()
Dim sh As Worksheet, rngSUM As Range, arrSUM, i As Long
Set sh = Workbooks("Test.xlsm").Sheets("Test")
Set rngSUM = sh.Range("CO66:CS69,CO88:CS91,CO95:CS98") 'The whole discontinuous range
ReDim arrSUM(1 To 4, 1 To 1) 'ReDim the array to keep processing result
For i = 1 To rngSUM.rows.Count 'iterate between the discontinuous range rows and summarize them:
arrSUM(i, 1) = WorksheetFunction.Sum(rngSUM.rows(i))
Next i
sh.Range("I41").Resize(4).Value = arrSUM 'drop the array content at once
End Sub
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/407578.html
標籤：