【Excel VBA】特定の列で時間を入れているセルだけ合計して数式をセルにいれたい

質問

Excel VBA初心者です。

特定の列の各セル（B列とします）に時間（数字）、件数（数字）、文字列（○や×）が入っています。
この中でランダムな位置に入っている時間（数字）のみ拾い出し、合計欄（セルB20とします）に「数式（例）=sum(b3,b6,b8,b13,b18)」を入れたいのですが、マクロの組み方がわかりません。上記の特定のセルを抽出するところまではできたのですが。

マクロの組み方をご教授願えますか。

回答No.5

hotosys · ベストアンサー

No.1です。
AA列に対象セルの一覧がある場合はこんなのではどうでしょうか？
Sub sample()
Dim lastRow As Long
Dim rngStr As String
Dim r As Long
’AA列の最終行取得
lastRow = Range("AA" & Rows.Count).End(xlUp).Row
'1列から最終行まで
For r = 1 To lastRow
'rngStrが空白でない場合(2個目以降)はカンマを付ける
If rngStr <> "" Then rngStr = rngStr & ","
'セルを追加
rngStr = rngStr & Range("AA" & r).Value
Next
Range("B20").Formula = "=sum(" & rngStr & ")"
End Sub

実際は上記よりも、AA列を作る時点で計算をすれば、AA列を作る必要が無いと思います。
合計の変数を用意して
Range("AA1") = "b3"
または、
Cells(1, 27) = "b3"
にする場所で、AA列に代入せずに、その値を利用して、合計変数にセルの値を加えればいいと思います。
ただ、質問の内容から細かい仕様がわからないので、既に出来ているAA列があるなら、それを利用して計算するのも間違いない方法だと思います。

お礼

おかげさまで解決できました。
説明が行き届かずご迷惑おかけしましたが、丁寧にご対応
いただきありがとうございました。

回答No.4

imogasi

エクセルの日付・時刻シリアル値の考えを知っていますか。質問振りから、多分知らないのでは。ここが肝心。エクセルでは、日付や時刻は正式な（標準の）場合は、セルの値は数値なのだ。だから普通にセルの値だけ考えると、両者は区別がつかない。
そこで数値を日付や時刻たらしめているのは、表示形式なのです。
だからセルの表示形式をチェックしないとならない。＞Excel VBA初心者です、には勉強しないと難しい。
それにVBAで式を作ると言うのもあまりやらないと思う。
まあ式を入れてしまえばそのセルの値の変更に即反応してくれるメリットは有るが。VBAで作った式は、関連条件（式の引数部）が変わると、自動的には変わらない。
ある列に日付時間や、数値や四号など入れる表の設計自体が、エクセルの良い使い方ではない。質問全体が、エクセルやVBAの経験不足をうかがわせる。日付・時刻を人間がある列に指定し、それに基づく処理をお勧めする。エクセル利用者は、普通は列でこの列には時刻を入れるとかで人間が決定しているのだ。
本件したいことを丸投げしただけで、本コーナーの丸投げ禁止に抵触する状態だ。
下記を参考に調べマクって考えてください。
B列B2:B19にデータがある場合
Sub test01()
For Each cl In Range("B2:B19")
MsgBox cl.NumberFormatLocal
Next
End Sub
これで日付・時刻セルの書式は見覚えのあるものが（例ｙｙｙｙ/ｍ/ｄやｈ：ｍｍなど）出てくる。しかし日付・時刻書式は多種多様でその型から日付型・時刻型だを割り出すのは手間だし完全でない。
表示形式の日付型、時刻型をズバリ表すコードは小生、学不足で見たことがない。
セルについて日付時刻が判れば、
Sub test01()
For Each cl In Range("B2:B19")
'MsgBox cl.NumberFormatLocal
MsgBox cl.Address(False, False)
Next
End Sub
のAddress(False, False)でB2と言った＄付きでない番地文字列が判る。
しかし連続した時刻の入ったセルは「：」で表現したいが、そのちょっとした、スタートセル・エンドセルを捉えるテクニックなんだが、初心者には難しいと思うが、勉強のこと。

お礼

ご指摘ありがとうございます。

私はExcel VBAに限らずプログラム全般について初心者です。
また、丸投げ禁止ということも知らず、質問自体も大変に
わかりづらく申し訳ありません。
※勉強してもう少しましな質問が出来るように努力します。

回答No.3

xls88

回答番号：No.2 で解らなければ、時間データセルを抽出するマクロを提示してください。

お礼

ご対応ありがとうございました。
おかげさまで解決しました。

回答No.2

xls88

数式を入れる操作を「マクロの記録」すれば参考コードが得られます。

Range("B20").Formula = "=SUM(" & 抽出セル範囲のアドレス & ")"

回答No.1

hotosys

>時間（数字）
の意味がわからないのですが、時刻型で24時間を超えない時刻
1:25
10:30
22:25
とかなら
vbaを使わなくても
B20=SUMIF(B1:B19,"<1")
でいいと思います。
時刻は内部的には24時を1とする数値のため、24時間を超えないなら1未満の数値のため、1未満の数値の合計で求まると思います。
B20のセルの書式は時刻型にしてください。
尚、合計時間の方は24時間を超えられますが、その場合[書式][セル][ユーザー定義]で
[h]:mm
と時間のhを[]で囲ってください。
そうしないと正しく表示しないと思います。

時刻型で無い場合は、時間をどのような数字で表して、件数と区別しているのでしょうか？
また、
>上記の特定のセルを抽出するところまではできたのですが。
で、どのような形で取り出してあるのでしょうか?

ちなみに
>合計欄（セルB20とします）に「数式（例）=sum(b3,b6,b8,b13,b18)」を入れたい
をvbaで行う場合は
Range("B20").Formula = "=sum(b3,b6,b8,b13,b18)"
などだと思います。

補足

さっそくのご回答ありがとうございます。

>>時間（数字）
>の意味がわからないのですが、時刻型で24時間を超えない時刻
>時刻型で無い場合は、時間をどのような数字で表して、件数と
>区別しているのでしょうか？
説明が足りずすみません。
このシートは作業者が色々な作業で対応した件数や作業した
時間を入力するシートです。
時間は作業時間のことで1時間であれば1、45分であれば0.75と
作業者が数字を入力しております。
作業時間と件数はセルの書式設定で7件とか1.5hと表示してい
ますが、実際にはそれぞれ数字（7とか1.5）を入力しています。

>>上記の特定のセルを抽出するところまではできたのですが。
>で、どのような形で取り出してあるのでしょうか?
現在試しているマクロを実行すると、時間が入っているセルを
拾い出し、AA1セルから下にセル位置が入るようにしています。
b3
b6
b8
b13
b18

>Range("B20").Formula = "=sum(b3,b6,b8,b13,b18)"
上記で拾い出したセルから何とかここにたどりつきたい
のですが。
シートによって作業時間が入力してある位置が違うため、
こういうシートも存在します。
b4
b7
b19
この場合はRange("B20").Formula = "=sum(b4,b7,b19)"
になる必要があります。
Range("B20").Formula = "=sum(b3,b6,b8,b13,b18)"や
Range("B20").Formula = "=sum(b4,b7,b19)"をマクロで
生成できればいいのですが。

お手数おかけしますが、よろしくお願いいたします。