范围和公式部分的excel vba数组？

Question

我不是很确定如何或者是否需要做多个数组来达到我的最终目标。

我必须对范围列D、E、F、G、H和I以及同一张表上的早上、中午、晚上和周末的公式部分执行此操作。早上的范围是1-75，中间的范围是76-150，晚上的范围是151-225，周末的范围是226-300。我想要做的就是压缩我的代码并将其清理干净。

这是我在周末公式部分的D列的一个例子。

Worksheets("InsightsData").Range("D226").Formula = "=Weekend!$B$82"
Worksheets("InsightsData").Range("D227").Formula = "=Weekend!$B$144"
Worksheets("InsightsData").Range("D228").Formula = "=Weekend!$B$206"
Worksheets("InsightsData").Range("D229").Formula = "=Weekend!$B$268"
Worksheets("InsightsData").Range("D230").Formula = "=Weekend!$B$330"
Worksheets("InsightsData").Range("D231").Formula = "=Weekend!$B$392"
Worksheets("InsightsData").Range("D232").Formula = "=Weekend!$B$454"
Worksheets("InsightsData").Range("D233").Formula = "=Weekend!$B$516"
Worksheets("InsightsData").Range("D234").Formula = "=Weekend!$B$578"
Worksheets("InsightsData").Range("D235").Formula = "=Weekend!$B$640"
Worksheets("InsightsData").Range("D236").Formula = "=Weekend!$B$702"
Worksheets("InsightsData").Range("D237").Formula = "=Weekend!$B$764"
Worksheets("InsightsData").Range("D238").Formula = "=Weekend!$B$826"
Worksheets("InsightsData").Range("D239").Formula = "=Weekend!$B$888"
Worksheets("InsightsData").Range("D240").Formula = "=Weekend!$B$950"
Worksheets("InsightsData").Range("D241").Formula = "=Weekend!$B$1012"
Worksheets("InsightsData").Range("D242").Formula = "=Weekend!$B$1074"
Worksheets("InsightsData").Range("D243").Formula = "=Weekend!$B$1136"
Worksheets("InsightsData").Range("D244").Formula = "=Weekend!$B$1198"
Worksheets("InsightsData").Range("D245").Formula = "=Weekend!$B$1260"
Worksheets("InsightsData").Range("D246").Formula = "=Weekend!$B$1322"
Worksheets("InsightsData").Range("D247").Formula = "=Weekend!$B$1384"
Worksheets("InsightsData").Range("D248").Formula = "=Weekend!$B$1446"
Worksheets("InsightsData").Range("D249").Formula = "=Weekend!$B$1508"
Worksheets("InsightsData").Range("D250").Formula = "=Weekend!$B$1570"
Worksheets("InsightsData").Range("D251").Formula = "=Weekend!$B$1632"
Worksheets("InsightsData").Range("D252").Formula = "=Weekend!$B$1694"
Worksheets("InsightsData").Range("D253").Formula = "=Weekend!$B$1756"
Worksheets("InsightsData").Range("D254").Formula = "=Weekend!$B$1818"
Worksheets("InsightsData").Range("D255").Formula = "=Weekend!$B$1880"
Worksheets("InsightsData").Range("D256").Formula = "=Weekend!$B$1942"
Worksheets("InsightsData").Range("D257").Formula = "=Weekend!$B$2004"
Worksheets("InsightsData").Range("D258").Formula = "=Weekend!$B$2066"
Worksheets("InsightsData").Range("D259").Formula = "=Weekend!$B$2128"
Worksheets("InsightsData").Range("D260").Formula = "=Weekend!$B$2190"
Worksheets("InsightsData").Range("D261").Formula = "=Weekend!$B$2252"
Worksheets("InsightsData").Range("D262").Formula = "=Weekend!$B$2314"
Worksheets("InsightsData").Range("D263").Formula = "=Weekend!$B$2376"
Worksheets("InsightsData").Range("D264").Formula = "=Weekend!$B$2438"
Worksheets("InsightsData").Range("D265").Formula = "=Weekend!$B$2500"
Worksheets("InsightsData").Range("D266").Formula = "=Weekend!$B$2562"
Worksheets("InsightsData").Range("D267").Formula = "=Weekend!$B$2624"
Worksheets("InsightsData").Range("D268").Formula = "=Weekend!$B$2686"
Worksheets("InsightsData").Range("D269").Formula = "=Weekend!$B$2748"
Worksheets("InsightsData").Range("D270").Formula = "=Weekend!$B$2810"
Worksheets("InsightsData").Range("D271").Formula = "=Weekend!$B$2872"
Worksheets("InsightsData").Range("D272").Formula = "=Weekend!$B$2934"
Worksheets("InsightsData").Range("D273").Formula = "=Weekend!$B$2996"
Worksheets("InsightsData").Range("D274").Formula = "=Weekend!$B$3058"
Worksheets("InsightsData").Range("D275").Formula = "=Weekend!$B$3120"
Worksheets("InsightsData").Range("D276").Formula = "=Weekend!$B$3182"
Worksheets("InsightsData").Range("D277").Formula = "=Weekend!$B$3244"
Worksheets("InsightsData").Range("D278").Formula = "=Weekend!$B$3306"
Worksheets("InsightsData").Range("D279").Formula = "=Weekend!$B$3368"
Worksheets("InsightsData").Range("D280").Formula = "=Weekend!$B$3430"
Worksheets("InsightsData").Range("D281").Formula = "=Weekend!$B$3492"
Worksheets("InsightsData").Range("D282").Formula = "=Weekend!$B$3554"
Worksheets("InsightsData").Range("D283").Formula = "=Weekend!$B$3616"
Worksheets("InsightsData").Range("D284").Formula = "=Weekend!$B$3678"
Worksheets("InsightsData").Range("D285").Formula = "=Weekend!$B$3740"
Worksheets("InsightsData").Range("D286").Formula = "=Weekend!$B$3802"
Worksheets("InsightsData").Range("D287").Formula = "=Weekend!$B$3864"
Worksheets("InsightsData").Range("D288").Formula = "=Weekend!$B$3926"
Worksheets("InsightsData").Range("D289").Formula = "=Weekend!$B$3988"
Worksheets("InsightsData").Range("D290").Formula = "=Weekend!$B$4050"
Worksheets("InsightsData").Range("D291").Formula = "=Weekend!$B$4112"
Worksheets("InsightsData").Range("D292").Formula = "=Weekend!$B$4174"
Worksheets("InsightsData").Range("D293").Formula = "=Weekend!$B$4236"
Worksheets("InsightsData").Range("D294").Formula = "=Weekend!$B$4298"
Worksheets("InsightsData").Range("D295").Formula = "=Weekend!$B$4360"
Worksheets("InsightsData").Range("D296").Formula = "=Weekend!$B$4422"
Worksheets("InsightsData").Range("D297").Formula = "=Weekend!$B$4484"
Worksheets("InsightsData").Range("D298").Formula = "=Weekend!$B$4546"
Worksheets("InsightsData").Range("D299").Formula = "=Weekend!$B$4608"
Worksheets("InsightsData").Range("D300").Formula = "=Weekend!$B$4670"

按照我的方式，现在会产生大约1800行代码。必须有一种更简单的方法来做到这一点。

Answer 1

“必须有一种更简单的方法来做到这一点。”

是的，一个循环和一些数学运算：

Dim i as Long
For i = 0 to 74
    Worksheets("InsightsData").Range("D" & 226 + i).Formula = "=Weekend!$B$" & 82 + i*62
Next