HOME > Q&A > Excel （VBA）

Excel （VBA）
Excel VBAに関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 10全般 : Microsoft 365)
短く簡略化出来ますか？
投稿日時: 22/10/25 10:47:48
投稿者: 破傷庵

いつも大変お世話になっております。よろしくお願いします。
下記のコードは簡略に記述する事が出来ますか。
 
Sub test()
'
 
' test Macro
'
 
    Application.ScreenUpdating = False
 
 
    Select Case Range("P1")
       
    Case Is = 4
    Range("O3").Formula = "=IF($P$1=4,SUM($B3:$B3),0)"
     
    Case Is = 5
    Range("O3").Formula = "=IF($P$1=5,SUM($B3:$C3),0)"
     
    Case Is = 6
    Range("O3").Formula = "=IF($P$1=6,SUM($B3:$D3),0)"
     
    Case Is = 7
    Range("O3").Formula = "=IF($P$1=7,SUM($B3:$E3),0)"
      
    Case Is = 8
    Range("O3").Formula = "=IF($P$1=8,SUM($B3:$F3),0)"
          
    Case Is = 9
    Range("O3").Formula = "=IF($P$1=9,SUM($B3:$G3),0)"
     
    Case Is = 10
    Range("O3").Formula = "=IF($P$1=10,SUM($B3:$H3),0)"
       
    Case Is = 11
    Range("O3").Formula = "=IF($P$1=11,SUM($B3:$I3),0)"
        
    Case Is = 12
    Range("O3").Formula = "=IF($P$1=12,SUM($B3:$J3),0)"
         
    Case Is = 1
    Range("O3").Formula = "=IF($P$1=1,SUM($B3:$K3),0)"
     
    Case Is = 2
    Range("O3").Formula = "=IF($P$1=2,SUM($B3:$L3),0)"
     
    Case Is = 3
    Range("O3").Formula = "=IF($P$1=3,SUM($B3:$M3),0)"
     
    End Select
 
     
    Range("O3").Select
    Selection.Copy
    Range("O4:O120").Select
    Selection.PasteSpecial Paste:=xlPasteFormulas
    Range("P1").Select
    Application.CutCopyMode = False
     
    Application.ScreenUpdating = True[/u]
 
 
End Sub
 
赤線青線はどの様な事ですか。
８１歳の超初心者です。よろしくお願いします。
 
 
[/u]
回答
投稿日時: 22/10/25 11:49:56
投稿者: taitani
投稿者のウェブサイトに移動

すみません、回答を行う前に、2点確認です。
Q1.コードを簡略にする理由は何でしょうか。
Q2.「赤線青線はどの様な事ですか。」この意味が分かりません。
具体的に教えてください。
回答
投稿日時: 22/10/25 12:03:39
投稿者: sk

・P1 セルに格納されている値の意味
 
・B 列から M 列 までの各列に格納されている値の意味
 
・O 列に設定しようとしている数式による計算の目的
 
とりあえず、以上の点について明記されることをお奨めします。
 

引用:
Select Case Range("P1")
    
    Case Is = 4
        Range("O3").Formula = "=IF($P$1=4,SUM($B3:$B3),0)"
  
    Case Is = 5
        Range("O3").Formula = "=IF($P$1=5,SUM($B3:$C3),0)"

例えば、ある年度におけるそれぞれの月の何らかの集計値（金額だか件数だか）が
B 列（当年 4 月）から M 列（翌年 3 月）までの 3 行目以降の各セルに格納されていて、
基準となる月を表す整数（ 1 〜 12 ）を P1 セルに入力することによって
4 月から基準月までの累計値を行ごとに計算したい、ということなのであれば、
まず O 列に設定する数式自体を見直された方がよいのではないでしょうか。
回答
投稿日時: 22/10/25 12:04:57
投稿者: Suzu

セル P1の値によって、O列の 合計関数 SUMの 集計対象セル範囲が変わるのですね。
 
　　　　　A1形式　　R1C1形式
1　のとき、B〜K　　列 2〜11
2 のとき B〜L　　列 2〜12
3　のとき　B〜M　　列 2〜13
4　のとき　B のみ　列 2 のみ
5　のとき　B〜C　　列 2〜3
　：
12のとき　 B〜J　　列 2〜10
 
それを、Q列の 各行にコピーしています。
 
 
A1形式の数式で考えると 場合分けをしないといけませんが、
R1C1形式で考えてみると、列のアルファベットを使わず、数値で列を表す事ができます。
　上記表の R1C1形式
 
また、計算範囲の指定として、
OFFSET関数を使い基準セルから、列方向に何個ずれの位置までをSUM関数の対象とするという事ができます
 
 
上記から O列に入れる数式は
　=SUM(OFFSET(RC2,,,,IF(R1C16<4,R1C16+9,R1C16-3)))
で良くなります。
 
R1C1形式の数式を Rangeに指定するには、FormulaR1C1 プロパティに渡します。
 
なので、 

Sub Sumple()
　Range("O3:O120").FormulaR1C1 = "=SUM(OFFSET(RC2,,,,IF(R1C16<4,R1C16+9,R1C16-3)))"
End If
で良いです。
 
 
引用:
赤線青線はどの様な事ですか。

線が見当たらないので、判りかねます。
投稿日時: 22/10/25 13:08:22
投稿者: 破傷庵

老人会会の会計帳簿で月毎の集計表を作りたいと考えてのものです。
横に月、縦に勘定科目になっています。
 
taitani様　有難うございます。
1　何度も繰り返すコードになっているので、短くなる方法があるのかと思って。
2　コードをコピーしたら赤線青線なっているいる部分ありましたが、送信したら消えていました。
 　 [u]Application.CutCopyMode = False
    Application.ScreenUpdating = True
 
sk様　有難うございます。
PIの値は　４月から翌年3月までの月の値です。
・B 列から M 列 までの各列に格納されている値の意味
各月の集計値です。
　B前年度繰越額　Cは４月集計額で　5月Dで　翌年3月がMです。
・O 列に設定しようとしている数式による計算の目的
　前月分までの集計額です。当月の集計表を作る時の為に。
・4 月から基準月までの累計値を行ごとに計算したい、ということなのであれば、
　まず O 列に設定する数式自体を見直された方がよいのではないでしょうか。
　O3の列にIF関数を使って長い式を作りました。その式をO列の4行目から50行目迄コピーしました。
　P1の値（月）が変わってもいいようにしたのですが、何かの拍子に式を消してしまったりするもですから。
　と思ってVBAでと思いました。
 
Suzu様　有難うございます。
=SUM(OFFSET(RC2,,,,IF(R1C16<4,R1C16+9,R1C16-3)))
　OFFSET関数のことを勉強します。
　R1C1形式は位置の認識が上手く理解できてないので、これから練習します。
　RC2,,,,　9,R　この部分に青い下線ついています。でも送信すると消えてしまいます。
 
皆様にお世話になります。有難うございました。
回答
投稿日時: 22/10/25 13:35:45
投稿者: taitani
投稿者のウェブサイトに移動

引用:

1　何度も繰り返すコードになっているので、短くなる方法があるのかと思って。
2　コードをコピーしたら赤線青線なっているいる部分ありましたが、送信したら消えていました。
 　 [u]Application.CutCopyMode = False
    Application.ScreenUpdating = True

 
何度も繰り返すコードにはなっていないですよ。
Select Case Range("P1") の値によって、Select Case の分岐で 1行だけ実行されるので。
 
Application.CutCopyMode = False
→これは、Excelの ショートカットの Ctrl+X みたいな命令で、
False なので、切り取りではないよ という意味です。
 
Application.ScreenUpdating = True
→これは、画面更新をするかどうか。
最初に、False しているので、画面更新 (描画) を停止しているので、停止を再開しているということです。
 
回答
投稿日時: 22/10/25 13:38:31
投稿者: taitani
投稿者のウェブサイトに移動

あと、計算式を O3 セルに入れるということが着地であれば、VBA を使わずに、関数だけで済みそうだと思います。
また、会計帳簿であれば、モーグのひな型に存在しているかもしれません。
 
https://www.moug.net/freesoft/
回答
投稿日時: 22/10/25 13:48:47
投稿者: WinArrow
投稿者のウェブサイトに移動

Select Case 〜 End Seelct
Ｏ３セルの数式をＯ１２０まで複写
 
を一括で処理する方法 

Dim P As Long
    With Range("P1")
        P = IIf(.Value <= 3, .Value + 12, .Value)
    End With
    Range("O3:O120").Formula = "=SUM(B3:" & Cells(3, P - 4 + 2).Address(0, 0) & ")"

回答
投稿日時: 22/10/25 14:25:22
投稿者: Suzu

引用:
Application.CutCopyMode = False

セルを選択し、Ctrl + C や、Ctrl + X で、コピーやカットを行うと、
選択したセルが 破線で 囲まれた状態になりますよね。
 
この状態から、適当なセルを選択し、 Ctrl + V を行うと、貼り付けが行われます。
この時、コピー・カットを行った、最初に選択したセルは、まだ破線で囲まれた状態です。
 
さらに、別のセルを選択し、続けて Ctrl + V を行うと、更に同じ値が貼り付けられます。
破線で囲まれた範囲について、改めて貼り付けられました。
 
ここで、Esc を押すと、破線の囲みは解除されます。
別のセルを選択し、　Ctrl + V を行っても、貼り付けは行われません。
この Esc を 押すのと同じ命令と思えば良いです。
 
 
引用:
　RC2,,,,　9,R　この部分に青い下線ついています。でも送信すると消えてしまいます。

これは、どこでそうなっているのでしょうか？ Excelの数式バーでしょうか？
その青線は、破傷庵 さんが付けているのでしょうか？
その場合、それは、どうやってでしょうか？
 
この掲示板の使い方としては、
文字の入力フォームに文字を入力し、下線や色を付けたい場合、
その範囲を選択し BBコード 右の 太字〜コード を選択、文字の色を選択すると
その選択範囲の前後にタグが入り、掲示板上で色が付いたりします。
送信の前に、プイビューで確認する様にしましょう。
 
 
 
 
引用:
=SUM(OFFSET(RC2,,,,IF(R1C16<4,R1C16+9,R1C16-3)))
　OFFSET関数のことを勉強します。
　R1C1形式は位置の認識が上手く理解できてないので、これから練習します。

 
R1C1形式ではなく、慣れているであろう A1形式ですと
O3 の数式は 
=SUM(OFFSET($B3,,,,IF($P$1<4,$P$1+9,$P$1-3)))

です。
 
これは、VBAは関係ありません。
O3 セルを選択し、そのセルの数式に指定してみてください。
その上で、オートフィルで、O120まで拡張します。
 
そうると、P1のセル値により、得たいであろう値が得られます。
 
P1 の値を更新した後に、VBAを走らせる必要はなく、数式だけで構成されています。
投稿日時: 22/10/25 14:46:47
投稿者: 破傷庵

taitani様 WinArrow様　Suzu様　何度も申し訳ございません。有難うございます。
ご回答ありがとうございました。
教えていただいた事をこれから勉強いたします。
解らない事があれば、また、質問させていただきます。よろしくお願いいたします。
一先ず、解決済にさせて頂きます。
深く感謝申し上げます。