[[20240728063236]]　『For Nextマクロの手直し』（じいかっぱ）　ページの最後に飛ぶ

『For Nextマクロの手直し』（じいかっぱ）

皆様からの支援で下記のようなマクロが作成でき喜んでいたところ、

 Sub 数式入力3()
  Dim kurikaesi As Long
  Dim kurikaesi2 As Long
  Dim s As String

   For kurikaesi = 2 To 5

    s = Sheets("Sheet2").Range("C" & kurikaesi).Offset(, -1).Value
    Range("C" & kurikaesi).Formula = "=SUMIF(出納帳!B4:B204," & Chr(34) & s & Chr(34) & ",出納帳!D4:D204)"

   Next kurikaesi

   For kurikaesi2 = 8 To 32

    s = Sheets("Sheet2").Range("C" & kurikaesi2).Offset(, -1).Value
    Range("C" & kurikaesi2).Formula = "=SUMIF(出納帳!B4:B204," & Chr(34) & s & Chr(34) & ",出納帳!E4:E204)"

   Next kurikaesi2

 End Sub

友人から、今後項目の変更・追加、特に追加に対応したものができないかと言われ、空白行をそれぞれ2行追加して以下のように手を加えたところ・・・

 Sub 数式入力3()
  Dim kurikaesi As Long
  Dim kurikaesi2 As Long
  Dim s As String

   For kurikaesi = 2 To 7

    s = Sheets("Sheet2").Range("C" & kurikaesi).Offset(, -1).Value
    Range("C" & kurikaesi).Formula = "=SUMIF(出納帳!B4:B204," & Chr(34) & s & Chr(34) & ",出納帳!D4:D204)"

   Next kurikaesi

   For kurikaesi2 = 10 To 36

    s = Sheets("Sheet2").Range("C" & kurikaesi2).Offset(, -1).Value
    Range("C" & kurikaesi2).Formula = "=SUMIF(出納帳!B4:B204," & Chr(34) & s & Chr(34) & ",出納帳!E4:E204)"

   Next kurikaesi2

 End Sub

当然のことですが、C列の左セルの値「Offset(, -1).Value」が空白の場合、それぞれ「0」となってしまいました。
友人は「C列の左セルの値「Offset(, -1).Value」が空白の場合には、この0表示が出ないようにしてほしいなあ」と注文してきました。
どのように手直しをしたらよいか、ご教授願えませんでしょうか。

＜使用 Excel：unknown、使用 OS：unknown ＞

ざっと以下ではどうですか　？　（未検証）

 Sub 数式入力3()
    Dim kurikaesi As Long
    Dim kurikaesi2 As Long
    Dim s As String

    For kurikaesi = 2 To 7
        s = Sheets("Sheet2").Range("C" & kurikaesi).Offset(, -1).Value
        If Trim(s) <> "" Then
            Range("C" & kurikaesi).Formula = "=SUMIF(出納帳!B4:B204," & Chr(34) & s & Chr(34) & ",出納帳!D4:D204)"
        Else
            Range("C" & kurikaesi).ClearContents
        End If
    Next kurikaesi

    For kurikaesi2 = 10 To 36
        s = Sheets("Sheet2").Range("C" & kurikaesi2).Offset(, -1).Value
        If Trim(s) <> "" Then
            Range("C" & kurikaesi2).Formula = "=SUMIF(出納帳!B4:B204," & Chr(34) & s & Chr(34) & ",出納帳!E4:E204)"
        Else
            Range("C" & kurikaesi2).ClearContents
        End If
    Next kurikaesi2
 End Sub
（暇な人） 2024/07/28(日) 06:57:14

 C2セルに手入力する場合はこんな式
 =IF(B2="","",SUMIF(出納帳!$B$4:$B$204,B2,出納帳!$D$4:$D$204)

 これをマクロで入力する場合　：　　※式の途中の " は、2個重ねて ""　とする
 Range("C2").Formula= "=IF(B2="""","""",SUMIF(出納帳!$B$4:$B$204,B2,出納帳!$D$4:$D$204)"

 C2;C7に同じ式を入力の場合　
 Range("C2:C7").Formula= "=IF(B2="""","""",SUMIF(出納帳!$B$4:$B$204,B2,出納帳!$D$4:$D$204)" 
 　　
（マナ） 2024/07/28(日) 07:47:15

 同様に、C10;C36に式を入力するには
 Range("C10:C36").Formula= "=IF(B10="""","""",SUMIF(出納帳!$B$4:$B$204,B10,出納帳!$E$4:$E$204)" 
                                                                            ~~~~
（マナ） 2024/07/28(日) 08:02:06

 For～Nextで入力する場合は   ※面倒なので、わたしは使いませんが。
 For k = 10 To 36
     Range("C" & k).Formula= "=IF(B" & k & "="""","""",SUMIF(出納帳!B4:B204,B" & k & ",出納帳!E4:E204)" 
 Next k
（マナ） 2024/07/28(日) 08:11:35

 前回のスレッドは、
[[20240724105448]] です。
 なんかこちらの提案（セル参照を使って数式そのものを変更すればよい）が通じていなかったみたいですね。残念!
（xyz） 2024/07/28(日) 13:50:33

暇な人様、マナ様、xyz様
ご教授ありがとうございました。
暇な様のご提案をそのまま試したところ、すんなりと希望が叶いました。
今回はこれを使わせていただくことにします。

それぞれのご提案をじっくり時間をかけて読み込み、小生なりに理解して、今後に生かしていきたいと思います。
（じいかっぱ） 2024/07/29(月) 06:07:31

話はおわっているようですが何点か。

■１
前トピックでコメントしませんでしたが、ExcelVBAの世界では基本的にシートやセル（オブジェクトといいます）は、きちんと明示すればいちいちアクティブにしたり選択したりする必要はありません。

また、【標準モジュール】でシートの指定を省略した場合、ActiveSheetを指定したものとみなされるルールです。
なので、たとえば【出納帳】シートがアクティブのときに↓を実行すると【出納帳】シートのＣ列に数式が書き込まれます。

 Range("C" & kurikaesi).Formula = ～～～～

このように、想定外の動作になりかねないことから、個人的にはActive○○やSelection.○○のように、アクティブなものや選択しているものに依存するような記述はお勧めしません。

■２
既に指摘があることですが、[[20240724105448]]で提示したようにループをする必要がありません。
上記も踏まえると、最初に提示されたものと2番目に提示されたものはそれぞれ↓のようにすれば、2行のコードでほぼ同様の結果が得られます。

    Sub 最初に提示されたもの()
        Sheets("Sheet2").Range("C2:C5").Formula = "=SUMIF(出納帳!$B$4:$B$204,B2,出納帳!$D$4:$D$204)"
        Sheets("Sheet2").Range("C8:C32").Formula = "=SUMIF(出納帳!$B$4:$B$204,B2,出納帳!$E$4:$E$204)"
    End Sub
    '-----------------------------------
    Sub 二番目に提示されたもの()
        Sheets("Sheet2").Range("C2:C7").Formula = "=SUMIF(出納帳!$B$4:$B$204,B2,出納帳!$D$4:$D$204)"
        Sheets("Sheet2").Range("C10:C36").Formula = "=SUMIF(出納帳!$B$4:$B$204,B2,出納帳!$E$4:$E$204)"
    End Sub

■３
なので、マナさんが提示されているようにB列が""のときは""を返すような数式を入れればよいのであれば↓のようにすればよい話だったりします。

    Sub 改善した数式を一気に書き込む()
        Sheets("Sheet2").Range("C2:C7").Formula = "=IF(B2="""","""",SUMIF(出納帳!$B$4:$B$204,B2,出納帳!$D$4:$D$204)"
        Sheets("Sheet2").Range("C10:C36").Formula = "=IF(B10="""","""",SUMIF(出納帳!$B$4:$B$204,B10,出納帳!$E$4:$E$204)"
    End Sub

■４
なお、使える状況は限定されますが、以下のように項目がそれぞれ固まっていて、かつ、独立している（くっついていない）場合ならば、下記のようなアプローチでもよいと思います。

    Sub B列に文字列があるセルだけ数式を書き込む()
        Dim MyRNG As Range

        With Worksheets("Sheet2")
            On Error Resume Next
            Set MyRNG = .Range("B:B").SpecialCells(xlCellTypeConstants, 2)
            On Error GoTo 0

            If MyRNG Is Nothing Then
                MsgBox "B列に定数（文字列）が見つかりませんので処理できません。"
                Exit Sub
            End If

            If MyRNG.Areas.Count > 1 Then
                MyRNG.Areas(1).Offset(, 1).FormulaR1C1 = "=SUMIF(出納帳!R4C2:R204C2,RC[-1],出納帳!R4C4:R204C4)"
                MyRNG.Areas(2).Offset(, 1).FormulaR1C1 = "=SUMIF(出納帳!R4C2:R204C2,RC[-1],出納帳!R4C5:R204C5)"
            Else
                MsgBox "項目範囲がくっついていますので処理できません。"
            End If
        End With
    End Sub

（もこな２） 2024/07/29(月) 19:00:32

コメント返信：

[ 一覧(最新更新順) ]

[[20240728063236]] 『For Nextマクロの手直し』（じいかっぱ） ページの最後に飛ぶ

[[20240728063236]]　『For Nextマクロの手直し』（じいかっぱ）　ページの最後に飛ぶ