Excel (VBA)

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

 
(Windows 10全般 : Excel 2013)
変数のかけ算(ワークシートの数式バーに数式を入れる「例=A1*A3」 のように
投稿日時: 18/05/20 17:11:36
投稿者: ジョンのおばちゃん

お世話になります。どうぞよろしくお願いいたします。思案に余っています。お助け頂けたらとても嬉しいです。
1行ひと月のカレンダーを作っています。(例・日付31日の時は34行使います)。
日付は変化します。そのカレンダーの下に1行空けて集計欄を作ります。行が変数です。その行末から1行空けて業務集計表を作ります。
 
36行目・「業務集計表」という項目、37行目・「回数」、38行目・「単価」、39行目・「金額(回数 * 単価)」です。
F欄からR欄まで業務種別が1欄おきに入ります。(F欄「業務A」、G欄「Aの回数」(2つの欄でワンセットです)、H欄「業務B」、I欄「Bの回数」・・・といくつかセットがあります)
 
「回数」欄には月のうちの同じ業務をCountif関数で数えて入れるようにしています。
「単価」は私には分からないので使う人がワークシートに入れるようにします。
 
「G欄・金額」に『=G37*G38』 の数式を入れたいのです。(もちろん、「I欄・金額」に『=I37*I38』と他の欄も同じ形の数式を入れます)
 
行数を固定させたら簡単だと思いますが、ちょっと頑張ってどの月も日付下に 「1行」 空けて集計表を作りたいのです。
 
2欄毎に数式を入れるのは欄の数を変数にセットして「step 2」で対応させます。
 
sub 金額計算() 'F3:R34 まで入力あり    
    Dim 集計行 As Long    
    Dim 単価セル As Long '単価セルを選ぶための変数    
    Dim 金額 As Long    
   集計行 = Cells(Rows.Count, 6).End(xlUp).Row + 3    
     
   For 金額 = 7 To 20 Step 2    
      Cells(集計行 + 2, 金額).Formula = "=G37*G38" 'これなら計算できる    
   Next 金額    
    
    '↓エラーになる
    Cells(集計行 + 2, 金額).Value = Cells(集計行, 金額) * Cells(集計行 + 1, 金額)
End Sub    
 
エラーになる部分をワークシートの数式バーに 「=G37*G38」 と表示させ、使う人(友人)が単価を入れたら金額が計算されるようになったらとても嬉しいです。
 
VBAはとても難しいですが、少しずつ勉強しています。どうぞよろしくお願いいたします。
 
 
 
 

投稿日時: 18/05/20 17:18:26
投稿者: ジョンのおばちゃん

ごめんなさい、カレンダーは1列ひと月です。(1行ひと月は間違い)
F列→日、G列→曜日、H列→祝祭日、R列まであります。(F4から月末日まで1列です)

回答
投稿日時: 18/05/20 19:44:53
投稿者: simple

シートのレイアウトを,以下のような形式で示してもらうと、一目瞭然ですけどね。

             F列      G列     H       I
1
2
3
・・
31
32
33
34
35
36
37
38
39


G39セルに =G37*G38 を入力する動作をマクロ記録してみたら
参考になるコードが得られると思いますよ。
それを示して貰えますか?

投稿日時: 18/05/20 21:36:42
投稿者: ジョンのおばちゃん

さっそくお返事頂きましてありがとうございました。
 
マクロを作ってみました。
Sub aaaa()
Range("G39").Select
    ActiveCell.FormulaR1C1 = "=R[-2]C*R[-1]C"
    Range("H39").Select
End Sub
 
シートのレイアウトの件ですが、以下でお分かりいただけますか。画像をアップロードしました。
http://www.geocities.jp/masazumi_tomoko/180520karenda-tate.html
 
     F列    G列     H列         I列      J列 ・・・・・R列まで
 
1 (この行は空白)     
2     空白   空白     HからKはセル結合・年月記入        L以下の列は空白
3      日   曜日     祝日        予定(1)   ※ K以下はI・J列同じ
4      1   金(例)             I以降欄は空白(使用者が入力)
31    2月はこの行まで使う。
32    閏年の2月はこの行まで使う。
33    4,6,6,11月はこの行まで使う。
34   1,3,5,7,8,10,11月はこの行まで使う
35    空白
36   F:I セル結合し「業務集計表」        J列から空白
37   文字列・業務A  G列は「業務A」をF4;R34の間に探した回数 H:R欄同じく。
38   文字列・単価  空白(使用者が数字を入れる)       H:R欄同じく。
39   文字列・金額  回数*単価の計算結果を表示させたい    H:R欄同じく。
 
ひと月の日数が変わるので「業務集計表」を「F1」に作ったら良かったかと思っています。その場合は、行数は変わらないので 
range("G3") = "=G1*G2"
range("H3") = "=H1*H2"
・・・
と繰り返したら金額を計算する目的は達成できますが。ただその時、G3、H3、I3・・・と一つずつ作ってゆく他には方法はないのかと考えたりしています。変数を使って1行で書くのはやはり無理でしょうか。
 
お世話になりますがどうぞ宜しくお願い申し上げます。
 
 
 
 
 

回答
投稿日時: 18/05/20 21:51:53
投稿者: simple

マクロ記録をよくごらんいただくと、
GとかHとか指定する必要がないものになっています。
美味しい話ですよね。
 
で、

    For 金額 = 7 To 20 Step 2
         Cells(集計行 + 2, 金額).FormulaR1C1 = "=R[-2]C*R[-1]C"
    Next 金額
とするのはどうですか?
 
なお、折角ファイルをアップロードしていただきましたが、
セキュリティが心配なので拝見しておりません。
こちらのサイトの「利用上のお願い」でも、
そうした手法は避けていただくよう記されていると思います。

回答
投稿日時: 18/05/20 22:22:10
投稿者: WinArrow
投稿者のウェブサイトに移動

引用:

   For 金額 = 7 To 20 Step 2
       Cells(集計行 + 2, 金額).Formula = "=G37*G38" 'これなら計算できる
   Next 金額
  

この右辺は、常にG列固定なんですか?
Valueで格納するならば
 
   For 金額 = 7 To 20 Step 2
       Cells(集計行 + 2, 金額).vALUE = Cells(集計行,金額).Offset(-2).Value * Cells(集計行,金額).Offset(-1).value
   Next 金額
   
こんな方法もありですが・・・・
 
私見ですが
集計行を下にに配置するより、上に配置した方が、月によって行が変わることがないので
分かりやすいかと・・・思います。
 

投稿日時: 18/05/20 22:39:43
投稿者: ジョンのおばちゃん

simple 様、ありがとうございました。
 
さっそく試しました。うまくゆきました。何日も悩んでいたのが解決しました。
 
R1C1、取っつきにくくて敬遠していました。勉強します。ああいう方法もあったのかととても参考になりました。
なお、画像の件、失礼しました。あのHTMLは画面をキャプチャしたJPGだけが張り付いています。さっそく削除します。

投稿日時: 18/05/20 22:53:06
投稿者: ジョンのおばちゃん

WinArrow 様、教えていただきましてありがとうございました。
 
おっしゃるとおり、業務集計は上の行に持ってくる方が見やすいです。
実は集計を作るのはカレンダーが完成してから思いついたわけです。
今からトップに集計を持ってくるのはちょっときついのでとりあえず下に置いた次第です。
 
金額欄の件、試して見ましたが金額欄に「0」と表示されるようです。数式バーも「0」となっています。
 
どうも有難うございました。今後とも教えていただきたくお願い申し上げます。