Excel (VBA)

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

 
次の計算式をVBAでのコードどうすればいいのですか。
投稿日時: 21/12/21 12:39:49
投稿者: 破傷庵

VBAの80歳の手習い超初心者です。よろしくお願いします。
 
=IF(A1=4,0,IF(A1=5,A3,IF(A1=6,SUM(A3:B3),IF(A1=7,SUM(A3:C3),IF(A1=8,SUM(A3:D3),IF(A1=9,SUM(A3:E3),""))))))
答えはN2のセルに求めています。
 
この式をVBAのコードではどの様に記述すればいいのですか。
よろしくお願いいたします。

回答
投稿日時: 21/12/21 14:56:19
投稿者: WinArrow
投稿者のウェブサイトに移動

数式をVBAで記述するには?
という質問そのものは近いできますが、
「そのVBAを起動するタイミング」
「同じことを連続してじっこうするのか?」
など、背景や汎用性などを考慮する必要があります。
 
取り敢えず、そのようなことを全部排除して、
最も簡単なコードは
    Range("N2").Formula = "=IF(A1=4,0,IF(A1=5,A3,IF(A1=6,SUM(A3:B3),IF(A1=7,SUM(A3:C3),IF(A1=8,SUM(A3:D3),IF(A1=9,SUM(A3:E3),""""))))))"
 
です。
数式そのままのコードです。
これは、あなたが考えていt答えでしょうか?

回答
投稿日時: 21/12/21 15:25:57
投稿者: simple

こういうようなことでしょうか?
ユーザー定義関数にしてみました。
 

Sub test()
    Range("N2").Value = myCalc(Range("A1").Value)
End Sub

Function myCalc(n As Long) As Variant
    Dim v As Variant
    Select Case n
        Case 4: v = 0
        Case 5: v = [A3]
        Case 6: v = Application.Sum([A3:B3])
        Case 7: v = Application.Sum([A3:C3])
        Case 8: v = Application.Sum([A3:D3])
        Case 9: v = Application.Sum([A3:E3])
        Case Else
            v = ""
    End Select
    myCalc = v
End Function

ちなみに、[A3:B3]は Range("A3:B3")と同じです。
          [A3] は Range("A3") と同じです。
Application.Evaluateメソッドのヘルプを参照してください。
また、本来は.Valueを省略しないほうがいいのですが、安全かと思うときは省略しています。

回答
投稿日時: 21/12/21 16:00:32
投稿者: simple

ユーザー定義関数なので testは不要で、
N2セルに =myCalc(A1)と書きます、というのがよかったですね。

回答
投稿日時: 21/12/21 17:00:29
投稿者: mattuwan44

Sub test()
    Dim a As String
    
        Select Case Range("A1").Value
            Case 4: a = Cells(.Cells.Count).Address
            Case 5: a = "A3"
            Case 6: a = "A3:B3"
            Case 7: a = "A3:C3"
            Case 8: a = "A3:D3"
            Case 9: a = "A3:E3"
        End Select
        
        If a <> "" Then
            MsgBox WorksheetFunction.Sum(Range(a))
        End If
    End With
End Sub

 
選択肢が複数ある場合はSelect Caseの構文を使うと読みやすいかと思います。
 
基本的に、選択肢によって合計するセル範囲が変わるということと思いますので、
セル範囲を示す文字列を変えて合計すればいいかと思いました。
また、0を返すときには、絶対に使わない(空白である)セルを参照して合計してやればいいかと思います。
 
計算結果をどこに返していいかわからないので、メッセージボックスで表示してみました。
 

回答
投稿日時: 21/12/21 17:03:02
投稿者: mattuwan44

訂正>
 
 >Case 4: a = Cells(.Cells.Count).Address
 
Case 4: a = Cells(Cells.Count).Address
 
の間違いです。
すみません。

投稿日時: 21/12/21 18:27:12
投稿者: 破傷庵

 
WinArrowさん、simpleさん、mattuwan44さん。早速に回答をいただきありがとうございます。
 
>数式そのままのコードです。
>これは、あなたが考えていt答えでしょうか?
そうです。只、IF関数を沢山作ることに時間がかかりVBAのコードで簡単に記述出来ればと思いました。
 
 
simpleさん、mattuwan44さんのコードをそのまま頂いて試してみて見ます。
 
A2の数値が変わると加算されていく場所が変わり(答え)をN2のセルの求めたいと思いました。
A1のセルの値は月を表します。
A1=4の時は 0
A1=5の時は A3
A1=6の時は A3+B3
A1=7の時は A3+B3+C3
A1=8の時は A3+B3+C3+D3
A1=9の時は A3+B3+C3+D3+E3
答えを入れる欄は全てN2でIF関数をつかいましたが、本当はA1の値は1から12まであってIF関数を12回書くのが
大変だったものですからVBAのコードでと思いました。
 余談で申し訳ありません。今から8年前にはこのモーグで皆さんに色々教えていただいて、大変お世話になりました。参考書を買って少しは勉強して僅かばかりコードを書けたんですが。8年振りに老人会の会長をさせられて事務の係の人が苦労しているのを見て、お節介ながら、この様な方法があるといったばかりに、それじゃあ貴方考えてよと言われて、取り組み始めたばっかりに、何もかもすっかり忘れて。
皆さんにご迷惑をおかけする事になりました。これから勉強しますのでよろしくお願いいたします。
皆さんに頂いたコードをそのまま理解もせず使用して質問してはならないと思っています。
何卒、ご容赦ください。ここではこんな事を書いてはいけないのでしたね。
 

回答
投稿日時: 21/12/21 18:44:28
投稿者: mattuwan44

>答えを入れる欄は全てN2でIF関数をつかいましたが、本当はA1の値は1から12まであってIF関数を12回書くのが
大変

 
関数の方が管理がしやすいかと思います。
名前の定義とか使えばChoose関数とか使えませんかね?
 
あるいは作業列を使ったりすると数式が簡単になるかと思います。
1つのセルに数式を全部押し込めるから、数式が複雑になるのです。
 
Select Case の条件をシート上に一覧表にしておいて、
VlookUp関数で表引きしたりしたらメンテナンスを含めわかりやすいかと。
 
参考>>
ExcelのINDIRECT関数の使い方|セルや他のシートを参照する
https://office-hack.com/excel/indirect/

回答
投稿日時: 21/12/21 18:50:22
投稿者: simple

引数の場所に依存して計算対象が動くんですね。
# だんだん今の数式のほうが楽に感じられてきました。
# 乗りかかった船なんで、行くところまで行こうかと。
では、こんな風にしたらどうですか?
 

Function myCalc(r As Range) As Variant
    Dim v As Variant
    Select Case r.Value
        Case 4: v = 0
        Case 5 To 9: v = Application.Sum(r.Offset(2).Resize(1, r.Value - 4))
        Case Else
            v = ""
    End Select
    myCalc = v
End Function

ワークシート上での使い方は同じです。

回答
投稿日時: 21/12/21 18:56:21
投稿者: mattuwan44

ふと、思いましたが、
番号ではなく、入力規則のリストで、名前を選択するようにして、
名前でセル範囲を変えられるとより使いやすいかと。
 
マクロ使うとマクロの許可をしないと使えなかったり、
マクロを知らない人に使わせると逆に不便な場面が多々あるかと思います。
 
 

投稿日時: 21/12/21 19:45:34
投稿者: 破傷庵

皆さん。ありがとうございました
 
WinArrowさん。そのまま載せさせていただきました。上手く動きました。
       長いコードを分割する時は(_)使うんでしたか?
 
simpleさん。 そのまま載せさせていただきました。上手く動きました。
 
mattuwan44さん。そのまま載せさせていただきました。上手く動きました。
        デバックになりましたので(End With)削除いたしました。
        答えがMsgBoxになっています。N2のセルになる事を望んでいます。
 
全然理解できてない事ばかりです。これから勉強します。
ありがとうございました。

回答
投稿日時: 21/12/21 23:09:34
投稿者: simple

数式で

=IF($A$2>4, SUM(OFFSET($A$4,0,0,1,$A$2-4)),0)
ではどうですか?

回答
投稿日時: 21/12/21 23:13:10
投稿者: WinArrow
投稿者のウェブサイトに移動

VBAではなく、数式での対応策です。
Ifは2つです。
  
セルN2に
=IF(OR(A1<3,A1>12),"",IF(A1=4,0,SUM(INDIRECT(CHAR(65)&2&":"&CHAR(65+A1-5)&2))))
  
INDIRECT関数とCHAR関数を使っています。
CHAR関数は、数値を列英字に変換しています。
  
=CHAR(65) → "A" A1の数値-5を加算数れば、列を求めることができます。
 

回答
投稿日時: 21/12/21 23:22:21
投稿者: WinArrow
投稿者のウェブサイトに移動

simpleさんの数式の方がスマートですね・・・・・
 
A1セルに4〜12以外が入力する可能性を加味して
=IF(OR(A1<4,A1>12),"",IF(A1=4,0,SUM(OFFSET(A2,0,0,1,A1-4))))

回答
投稿日時: 21/12/29 15:23:41
投稿者: X-PIE

・A1には1〜12の整数しか入れない。
・12月の右に1月、2月、3月、と続く。
 
という前提で
 
=IF(A1=4,0,SUM(OFFSET(A3,0,0,1,MOD(A1+8,12))))

投稿日時: 21/12/30 11:40:53
投稿者: 破傷庵

多くの人から回答をいただきました。有難うございます。
体調を崩してしまい、お返事をすることが出来ず申し訳ありません。
 
理解できないところがいっぱいあるのですが、体調が回復しましたら、質問させてください。
そのままにしておくとご迷惑をお掛けしますので一旦閉じさせていただきます。
自分でも勉強をして、出直します。
皆さん本当にありがとうございました。