[[20220121160423]]　『マクロの変数を使って数式を代入しているのですが』（emidij）　ページの最後に飛ぶ

[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]

　

『マクロの変数を使って数式を代入しているのですがうまく代入できません。』（emidij）

下記マクロを実行すると、エラーにはならないのですが、該当セルに数式が代入されず、どう修正すればいいのか分からず困っております。
マクロ初心者の為、どなたかご教授頂ければ幸いです。
よろしくお願い致します。

Sub Macro6() 

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False


    Dim lRow As Long
    Dim lColumn As Long


    lRow = Cells(Rows.Count, "F").End(xlUp).Row
    lColumn = Range("F1").Value


    With Cells(6, 7).Formula = "=SUM(H6:" & Cells(6, lColumn * 2 + 7) & ")"
    Range("G6").AutoFill Destination:=Range("G6").Resize(lRow - 2, 1)
    End With


    With Cells(lRow - 1, lColumn * 2 + 8).Formula = "=SUM(" & Cells(6, lColumn * 2 + 8) & ":" & Cells(lRow - 2, lColumn * 2 + 8) & ")"
    Cells(lRow - 1, lColumn * 2 + 8).AutoFill Destination:=Cells(lRow - 1, lColumn * 2 + 8).Resize(1, lColumn)
    End With


    With Cells(lRow, lColumn * 2 + 8)
    .Formula = "=" & Cells(lRow - 1, lColumn * 2 + 8)
    End With


    With Cells(lRow, lColumn * 2 + 9).Formula = "=" & Cells(lRow, lColumn * 2 + 8) & "+" & Cells(lRow - 1, lColumn * 2 + 9)
    Cells(lRow, lColumn * 2 + 9).AutoFill Destination:=Cells(lRow, lColumn * 2 + 9).Resize(1, lColumn - 1)
    End With


    With Cells(lRow + 1, lColumn * 2 + 8).Formula = "=" & Cells(lRow, lColumn * 2 + 8) & "/$F$" & lRow
    Cells(lRow + 1, lColumn * 2 + 8).AutoFill Destination:=Cells(lRow + 1, lColumn * 2 + 8).Resize(1, lColumn - 1)
    End With


    ActiveWorkbook.Names.Add Name:="グラフ範囲", RefersToLocal:=Range(Cells(lRow - 1, lColumn * 2 + 7), Cells(lRow + 1, lColumn * 3 + 7))


    Application.ScreenUpdating = True
    Application.DisplayAlerts = True

End Sub

＜ 使用 Excel：Office365、使用 OS：Windows10 ＞

＞ With Cells(6, 7).Formula = "=SUM(H6:" & Cells(6, lColumn * 2 + 7) & ")"

この行は、何をしたくて書いたものでしょうか？
（ほかにもあります）

（わからん） 2022/01/21(金) 16:19

説明不足で申し訳ありません。
そして作成の途中段階なので多々間違いがあるかと思います。申し訳ありません。

With Cells(6, 7).Formula = "=SUM(H6:" & Cells(6, lColumn * 2 + 7) & ")"
の部分についてですが、

セルG6に、セルH6からセルF1（lColumn）に入力されている数字×2+7列分の範囲の合計出したいと思っております。
（emidij） 2022/01/21(金) 16:39

＞セルG6に、セルH6からセルF1（lColumn）に入力されている数字×2+7列分の範囲の合計出したい

「With」はどういう意味ですか？

（わからん） 2022/01/21(金) 16:51 

 >With Cells(6, 7).Formula = "=SUM(H6:" & Cells(6, lColumn * 2 + 7) & ")"
 >Range("G6").AutoFill Destination:=Range("G6").Resize(lRow - 2, 1)
 >End With


    With Range("G6")
        .FormulaR1C1 = "=SUM(RC[1]:RC[" & lColumn * 2 + 7 & "])"
        .AutoFill Destination:=Range("G6:G" & lRow)
    End With


 行いたいのは こんな事？

（ピンク） 2022/01/21(金) 19:38 

 >With Cells(6, 7).Formula = "=SUM(H6:" & Cells(6, lColumn * 2 + 7) & ")"
 >Range("G6").AutoFill Destination:=Range("G6").Resize(lRow - 2, 1)
 >End With


                ↓


    With Range("G6")
        .FormulaR1C1 = "=SUM(RC[1]:RC[" & lColumn * 2 + 7 & "])"
        .AutoFill Destination:=Range("G6:G" & lRow)
    End With


                ↓


    Range("G6:G" & lRow).FormulaR1C1 = "=SUM(RC[1]:RC[" & lColumn * 2 + 7 & "])"

（ピンク） 2022/01/21(金) 21:33

■１
既に指摘がありますが、Withステートメントについて誤解があるように思います。
↓を読んで確認されてはいかがでしょうか？
http://officetanaka.net/excel/vba/beginner/16.htm

■２
VBAではインデントの有無は実行上の意味を持つことはありません。（あってもなくても正常にうごきます）
一方で、インデントを適切につけることでコードの構造が把握しやすくなり、デバッグ作業の効率アップにつながると思いますので拘りがなければ、インデントをつけるようにすることをお勧めします。

■３
[[20220119164436]]　『マクロ実行時のエラーについて』（emidij）　
提示のコードを↑と同じ感じで整理してみると多分こんな感じになるとおもいます。 

    Sub Macro6_整理()
        'Application.ScreenUpdating = False
        Application.DisplayAlerts = False


        Dim lRow As Long
        Dim lColumn As Long


        With ActiveSheet
            lRow = .Cells(.Rows.Count, "F").End(xlUp).Row
            lColumn = .Range("F1").Value


            .Range("G6").Resize(lRow - 2, 1).Formula = "=SUM(H6:" & Cells(6, lColumn * 2 + 7) & ")"
            .Cells(lRow - 1, lColumn * 2 + 8).Resize(1, lColumn).Formula = "=SUM(" & Cells(6, lColumn * 2 + 8) & ":" & Cells(lRow - 2, lColumn * 2 + 8) & ")"
            .Cells(lRow, lColumn * 2 + 8).Formula = "=" & Cells(lRow - 1, lColumn * 2 + 8)
            .Cells(lRow, lColumn * 2 + 9).Resize(1, lColumn - 1).Formula = "=" & Cells(lRow, lColumn * 2 + 8) & "+" & Cells(lRow - 1, lColumn * 2 + 9)
            .Cells(lRow + 1, lColumn * 2 + 8).Resize(1, lColumn - 1).Formula = "=" & Cells(lRow, lColumn * 2 + 8) & "/$F$" & lRow


            ActiveWorkbook.Names.Add Name:="グラフ範囲", RefersToLocal:=Range(.Cells(lRow - 1, lColumn * 2 + 7), .Cells(lRow + 1, lColumn * 3 + 7))
        End With


        'Application.ScreenUpdating = True
        Application.DisplayAlerts = True
    End Sub

一旦落ち着いてそれぞれがどう関係してるのか見直してみてはいかがでしょうか？

（もこな２） 2022/01/21(金) 22:07

残念ながら反応なくなってしまいましたが、↑をエラーが出ないようにするならこんな感じでしょうか。 

    Sub Macro6_整理()
        'Application.ScreenUpdating = False
        Application.DisplayAlerts = False
        Dim lRow As Long
        Dim lColumn As Long
        With ActiveSheet
            lRow = .Cells(.Rows.Count, "F").End(xlUp).Row
            lColumn = .Range("F1").Value
            .Range("G6").Resize(lRow - 2, 1).Formula = "=SUM(H6:H" & Cells(6, lColumn * 2 + 7).Row & ")"
            .Cells(lRow - 1, lColumn * 2 + 8).Resize(1, lColumn).Formula = "=SUM(" & Cells(6, lColumn * 2 + 8).Address(False, False) & ":" & Cells(lRow - 2, lColumn * 2 + 8).Address(False, False) & ")"
            .Cells(lRow, lColumn * 2 + 8).Formula = "=" & Cells(lRow - 1, lColumn * 2 + 8).Address(False, False)
            .Cells(lRow, lColumn * 2 + 9).Resize(1, lColumn - 1).Formula = "=" & .Cells(lRow, lColumn * 2 + 8).Address(False, False) & "+" & .Cells(lRow - 1, lColumn * 2 + 9).Address(False, False)
            .Cells(lRow + 1, lColumn * 2 + 8).Resize(1, lColumn - 1).Formula = "=" & Cells(lRow, lColumn * 2 + 8).Address(False, False) & "/$F$" & lRow
            ActiveWorkbook.Names.Add Name:="グラフ範囲", RefersToLocal:=Range(.Cells(lRow - 1, lColumn * 2 + 7), .Cells(lRow + 1, lColumn * 3 + 7))
        End With
        'Application.ScreenUpdating = True
        Application.DisplayAlerts = True
    End Sub

（もこな２） 2022/01/23(日) 09:28

もこな２様
返信が遅くなり申し訳ございません。
教えて頂いた通りにやってみたところうまくいきました。
何分マクロ初心者の為、いろいろとネットで調べながら、やりたい処理を組み合わせておりまして、
たくさん突っ込みどころのある構文になっていると思います。
.Address(False, False)を付ければよかったのですね。
Withの役割についてもきちんと理解しておらず、
http://officetanaka.net/excel/vba/beginner/16.htm
上記のリンクも大変勉強になりました。
もっともっと勉強していきたいと思います。
本当にありがとうございました。

わからん様、ピンク様もご回答ありがとうございました。
（emidij） 2022/01/24(月) 14:10

コメント返信：

[ 一覧(最新更新順) ]

YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki. Modified by kazu.