VBA勉強中②

前回に続いて、Excelの作業をマクロで楽にするべく勉強中。

あるExcelのファイルには、拠点名がついたシートとそれをとりまとめる集計用のシートがある。集計用のシートで「=SUM(千葉集計!$AI$36:$AJ$185)」で各拠点シートの値の合計値を関数でひっぱってきたい。

• 思いついた方法①
  indirect関数　→　参照だけなら良いのだが、関数が中に入るとうまく動かない。
• 思いついた方法②
  各拠点のシートに合計値を表示して、その部分をindirectで拾う
  →　できなくはないが各シートにも加工が必要
• 思いついた方法③
  せっかくなので勉強をかねてVBAでマクロを作成

というわけで、一拠点分の=sum()の参照式を書いて全拠点分でコピペした後、拠点名を順番に変更するマクロを作成する。

マクロ例

Sub sample2()
　MsgBox "開始します"
　Dim i As Long
　Dim kyoushitu(34) As String
　kyoushitu(0) = "千葉"
　　〜中略〜
　kyoushitu(33) = "湘南台"
　For i = 6 To 38
　　' Cells(i, 55) = Replace(Cells(i, 55), "千葉", kyoushitu(i - 5))
　　Cells(i, 55).Formula = Replace(Cells(i, 55).Formula, "千葉", kyoushitu(i - 5))
　　Cells(i, 56).Formula = Replace(Cells(i, 56).Formula, "千葉", kyoushitu(i - 5)) '同じことをとなりの行にも実施
　Next i
　MsgBox "処理が終了しました"
End Sub

置き換え処理部分での注意

これだと値貼り付けになってしまった。

Cells(i, 55) = Replace(Cells(i, 55), "千葉", kyoushitu(i - 5))

以下で無事に解決

Cells(i, 55).Formula = Replace(Cells(i, 55).Formula, "千葉", kyoushitu(i - 5))

Replaceを使うとひとつ置き換えるたびに再計算が行われるのでそこそこ時間がかかりますが、手打ちしなくていいし目で見て進行がわかるのでこの方がおもしろいです。