エクセル マクロでＣ列に合計数が書式で出るようにしたい。

Question

マクロ初心者です。
教えてください。

###前提・実現したいこと
マクロでＣ列に合計数が書式で出るようにしたい。

###発生している問題・エラーメッセージ
CSVデータをシートに取り込みました。
同一ブック内に集計表の原紙シートを置き、マクロ実行でコピー＆集計されるようにしています。（添付画像）
For Each c In .Range("P1,X1,AA1")
のAA1の部分がCSVから取り込んだ合計数なんですが、当然、ただの数値なので、
集計値をいじっても反映されません・・・。

マクロの最後に、
'Ｃ列に合計
With .Cells(4, "C").Resize(.Rows.Count - 2)
.FormulaR1C1 = "=SUM(RC[1]:RC[" & dicE.Count & "])"
End With
としましたが、.Rowsで「参照が不正または不完全です。」と言われてしまいます。

Option Explicit

Private dstCol As Long

Sub 集計表改()

Dim c As Range, col As Long
Dim 集計表A1 As Variant
Dim d As Long
Dim v As Long

ActiveSheet.Copy Before:=Worksheets("CSV取り込み")

集計表A1 = ActiveSheet.Name
dstCol = 0
With Worksheets("CSV取り込み")
For Each c In .Range("P1,X1,AA1")
cpy c
Next c

For col = Columns("AN").Column To Columns("IA").Column Step 3
cpy .Cells(1, col)
Next col

End With

'表の色設定()
 Dim データ範囲 As Range
 Dim データ数 As Long
 Dim 行 As Long

 With Range("A4").CurrentRegion
 データ数 = .Rows.Count - 1
 Set データ範囲 = .Offset(2).Resize(データ数)
 End With
 データ範囲.Interior.ColorIndex = xlColorIndexNone
 For 行 = 2 To データ数 Step 3
  データ範囲.Rows(行).Interior.ColorIndex = 15
 Next
 Set データ範囲 = Nothing

With ActiveSheet.UsedRange
.EntireColumn.AutoFit '行の幅を自動調節
.Borders.LineStyle = xlContinuous  '枠線を引く
.HorizontalAlignment = xlCenter '数値を中央位置
With .Offset(3, 2).Resize(.Rows.Count - 3, .Columns.Count - 2)
.Font.Size = 16 '16point に大きくしています
.Font.Bold = True
End With
End With

 'inputboxでシート名の入力
   Dim ans As String  ' InputBoxの戻り
   Dim flg As Boolean  ' 数値かどうかの判定フラグ

    flg = False
   Do
    ans = InputBox("シート名を入力してください", "シート名入力")
    If StrPtr(ans) = 0 Then Exit Sub  ' キャンセル時に終了
    If IsNumeric(ans) Then flg = True
   Loop Until flg = True
    ActiveSheet.Name = ans
    'ﾎﾞﾀﾝを削除
    Dim Obj As Object
    For Each Obj In ActiveSheet.Shapes
      Obj.Select
      Application.DisplayAlerts = False
      Obj.Delete
      Application.DisplayAlerts = True
    Next Obj

End Sub

Private Sub cpy(Target As Range)
Dim st As Worksheet
Dim srcCol As Long
Dim lastRow As Long
Dim srcCells As Range

Set st = Target.Parent
srcCol = Target.Column

lastRow = st.Cells(Rows.Count, srcCol).End(xlUp).Row
Set srcCells = Range(st.Cells(2, srcCol), st.Cells(lastRow, srcCol))
srcCells.Copy Cells(4, dstCol + 1) '全体がずれる
dstCol = dstCol + 1

'    'Ｃ列に合計
'    With .Cells(4, "C").Resize(.Rows.Count - 2)
'         .FormulaR1C1 = "=SUM(RC[1]:RC[" & dicE.Count & "])"
'    End With

End Sub

Comments

[退会済みユーザー]

dicE.Count には何が入っていますか？

[marutoki]

すみません、このコードを他から持ってきたため、DicE.Countはこのマクロとは関係ないです。では、今回の範囲のどこを適用するかは分かりません(^-^;)

[Zuishin]

これマクロでやらなきゃダメですか？各店舗の数を横一列に合計すればいいんですよね？

[marutoki]

実際３行目の各店合計は関数でやってます。が、4行目以降はマクロで一度数値をいれるので事前に関数を入れておくことが出来ないです。現状、C3をコピペでやってますが、今日修正し忘れて出荷漏れがありました。

Answer 1

たぶん些細なミスでしょう。

    With .Cells(4, "C").Resize(.Rows.Count - 2)
         .FormulaR1C1 = "=SUM(RC[1]:RC[" & dicE.Count & "])"
    End With
    
        ↓
    With st.Cells(4, "C").Resize(st.Rows.Count - 2)
         .FormulaR1C1 = "=SUM(RC[1]:RC[" & dicE.Count & "])"
    End With

Comments

[hihijiji]

With は便利ですが、使い方によってはミスを誘発するので、
With ~ から
End With まではエディタ上でスクロールしない範囲に留めたほうが無難ですよ。

[marutoki]

上の方にも書きましたが、すみません、このコードを他から持ってきたため、DicE.Countはこのマクロとは関係ないです。では、今回の範囲のどこを適用するか・・、が今一つ理解できてないです(^-^;)

Answer 2

RC[1] というのは現在のセル(この場合は C4) から右に 1 動いたセルのことです。

R は row で縦、C は column で横を表し、この場合は C に 1 がついているので横に 1 進むという指示になります。正の数なので右です。

ここが sum の起点として指示されています。終点は右に dicE.Count 進んだセルですが、これが未定義なためエラーが出ているのでしょう。dicE.Count を削除してそこに店舗の数を入れたら取りあえず参照が不正な問題は解決すると思います。

しかし他にも色々問題を抱えたマクロです。直すべきところは多いと思います。マクロの勉強を基礎からやり直して理解できるようになるか、詳しい人に腰を据えて見てもらう必要があるかも知れません。

Answer 3

皆様、いろいろとご指導いただきありがとうございました。
いろいろ調べて、
Sub 集計表改()の最後に以下コードを入れて解決しました。
今後ともお力添えよろしくお願いします。

Dim myLastCol As Long
Dim myLastRow As Long

    'Ｃ列に合計
    myLastCol = Range("A4").End(xlToRight).Column '----最終列
    myLastRow = Range("C4").End(xlDown).Row '----最終行
    
    Cells(4, "C").Resize(myLastRow + 1).FormulaR1C1 = "=SUM(RC[1]:RC[" & myLastCol + 1 & "])"