VBA 時間の抜き出しが上手く処理できない

質問

時間の抜き出しをするのに下記のコードを候補に挙げましたが、
「'コロンが2個の場合　（時：分：秒）」の場合は上手く処理できますが
「'コロンが1個の場合　（分：秒）」の数値が上手く処理できません。

’-----------------------------------------------------------------------
Option Explicit

Sub コロンの数を数える()

Dim i As Long, cnt As Long, n As Variant

For i = 2 To Cells(Rows.Count, "A").End(xlUp).Row
cnt = 0 '←cntをリセット
Do
n = InStr(n + 1, Cells(i, "A"), ":")
If n = 0 Then
Exit Do
Else
cnt = cnt + 1
End If
Loop
If cnt < 1 Then
MsgBox "[:]がありません。" '←cntが1未満のときにメッセージを発出します。
End
Else
Cells(i, "B").Value = cnt
End If
Next
End Sub

Sub 時間抜き出し()

Dim i As Long, cnt As Long
Dim n As Single

For i = 2 To Cells(Rows.Count, "A").End(xlUp).Row
n = InStr(n + 1, Cells(i, "A"), ":") 'コロン「:」の位置を特定する
If Cells(i, "B") = 1 Then 'コロンが1個の場合　（分：秒）
Cells(i, "C").NumberFormatLocal = "h:mm:ss"
If Mid(Cells(i, "A"), n - 2, 1) = " " Or Mid(Cells(i, "A"), n - 2, 1) = "(" Then '10分以下の場合
Cells(i, "C") = Mid(Cells(i, "A"), n - 1, 4)

Cells(i, "C") = "0:" & Cells(i, "C")
Else '10分以上
Cells(i, "C") = Mid(Cells(i, "A"), n - 2, 5)
Cells(i, "C") = "0:" & Cells(i, "C")
End If
Else 'コロンが2個の場合　（時：分：秒）
Cells(i, "C").NumberFormatLocal = "h:mm:ss"
Cells(i, "c") = Mid(Cells(i, "A"), n - 1, 7)
End If
n = 0
Next

End Sub

回答No.6

kkkkkm · ベストアンサー

> chie65535さんも同じコードを推薦されてますが

そうですね。
先日から他の人を含め同じ内容の回答を後から出されますが、そういうのが流行ってるのかと思ってます(笑)
(私なら恥ずかしいので同じ回答を後からしません。違う手法とか数分後とかなら別ですが)
もしかしたら、私の説明ではNuboChanさんが理解できないと思ってやたら長い説明を入れるとかでしょうか。
それはそれでNuboChanさんにたいして失礼だと思いますが、まぁそういう事も多分無いでしょうから、私の回答が他の人には見えないのかもしれませんね。

> テキストエディターでコピペする時にNo2のお礼と一緒になってしまいました。

それで「-------------------」があったのですね。納得です。

回答No.5

kkkkkm

> ①、②は、③、④の60分の１になります。
> これを利用すれば欲しい数値になるのですが
> 何か上手くコンバートする方法はありますか　？

Cells(i, "C") = CDbl(TimeValue(Mid(Cells(i, "A"), n - 1, 4))) / 60

とすればいけると思いますが

先の回答(No.2)に記載した

Cells(i, "C") = "0:" & Mid(Cells(i, "A"), n - 1, 4)

のほうがわかりやすくないですか？

別の方への補足が目に入ったのですが(私宛のようでしたので)

> セル内の文字列には、
> 「分：秒」と「時：分：秒」が混在するので
> 最初から統一した「時：分：秒」フォーマットで処理はできません。

エクセルが11:22を11時間22分とデフォルトで認識するので「分：秒」の方を「時：分：秒」に合わせて処理する方向になっています。

お礼

>Cells(i, "C") = "0:" & Mid(Cells(i, "A"), n - 1, 4)
>のほうがわかりやすくないですか？

chie65535さんも同じコードを推薦されてますが
確かに
Cells(i, "C") = CDbl(TimeValue(Mid(Cells(i, "A"), n - 1, 4))) / 60
は難解でパット見て初心者の私には何をしているかが分かりにくいです。
（次回のコンバートの方法としてメモしておきます。）

>セル内の文字列には、
>「分：秒」と「時：分：秒」が混在するので
> 最初から統一した「時：分：秒」フォーマットで処理はできません。

すいません。
　上記は、No1さんへの回答（補足）です。
私の不手際で
テキストエディターでコピペする時にNo2のお礼と一緒になってしまいました。

回答No.4

imogasi

>「'コロンが1個の場合　（分：秒）」の
こいういうのは、質問するときの、記述表現としては、まずい。
時間部分なしの、分：秒をセルに入れるには、0:12:30のように入力しないとならない。そうやったとは
推測できない。
あるいはTIME 関数を使うとか。こちらは=TIME(0,2,30)のように区切りはカンマ。
もしVBAでセルに値をセットするなら、質問には、そう断る必要がある。
VBAではTimeSerial(hour,minute,second)　というのもある。
自分のやったこと（結果の状態を）を説明しないで（思った通りになっているとして）、その後のVBA処理がうまく行かないと質問するというのはおかしい。
エクセルの時刻に関する、微妙さ（常識でカバーできない難しさ）を分かってなくて、上のVBAを機論している。日付・時刻シリアル値に関して認識が甘いのでは。
大きな意味でＤｅｂｕｇのやり方を（質問回答に頼るのでなく）反省すべき」。
ーー
また日付を条件にして、データを抜き出す、フィルタする場合は、毎回WEBの関連記事を調べて、書き方をcチェックするぐらい慎重にすべき点だ。比較演算子も含めて、文字列で指定すべき場合が多いが、個人的には違和感があるのだが。

回答No.3

chie65536（@chie65535）

＞ 「'コロンが1個の場合　（分：秒）」の数値が上手く処理できません。

演算途中の途中結果をセルに格納してはいけません。

＞ Cells(i, "C").NumberFormatLocal = "h:mm:ss"
セルのフォーマットを時分秒に指定した状態で
＞ If Mid(Cells(i, "A"), n - 2, 1) = " " Or Mid(Cells(i, "A"), n - 2, 1) = "(" Then '10分以下の場合
＞ Cells(i, "C") = Mid(Cells(i, "A"), n - 1, 4)
の行で、切り出した「分：秒」をセルに格納すると「時：分：00」と解釈されて格納されます。
例えば最初の「12:15」をC列に格納すると、C列は「12:15:00」になります。
その後で
＞ Cells(i, "C") = "0:" & Cells(i, "C")
を行うと「0:12:15:00」を格納しようとするので、不正な結果になります。
＞ Else '10分以上
＞ Cells(i, "C") = Mid(Cells(i, "A"), n - 2, 5)
＞ Cells(i, "C") = "0:" & Cells(i, "C")
ここも、途中結果をセルに格納するので、おかしな結果になります。

以下のように修正して下さい。

Sub 時間抜き出し()

Dim i As Long, cnt As Long
Dim n As Single

For i = 2 To Cells(Rows.Count, "A").End(xlUp).Row
n = InStr(n + 1, Cells(i, "A"), ":") 'コロン「:」の位置を特定する
If Cells(i, "B") = 1 Then 'コロンが1個の場合　（分：秒）
Cells(i, "C").NumberFormatLocal = "h:mm:ss"
If Mid(Cells(i, "A"), n - 2, 1) = " " Or Mid(Cells(i, "A"), n - 2, 1) = "(" Then '10分以下の場合
Cells(i, "C") = "0:" & Mid(Cells(i, "A"), n - 1, 4)
Else '10分以上
Cells(i, "C") = "0:" & Mid(Cells(i, "A"), n - 2, 5)
End If
Else 'コロンが2個の場合　（時：分：秒）
Cells(i, "C").NumberFormatLocal = "h:mm:ss"
Cells(i, "c") = Mid(Cells(i, "A"), n - 1, 7)
End If
n = 0
Next

End Sub

お礼

chie65535さん、ありがとうございます。

"0:"を付加して同じセルに書き戻せれば良かったのですね。
1時間以下なので「分：秒」のフォーマットなのでもっと早く気が付くべきでした。

お陰様で、正常な表示になりました。

回答No.2

kkkkkm

一旦Cells(i, "C")に入れて"0:"を付加せずに直接付加してみてください。
Cells(i, "C") = Mid(Cells(i, "A"), n - 1, 4)
の場合その時点で2:15:00などになりそれに"0:"を付加するのでおかしなことになっています。

If Mid(Cells(i, "A"), n - 2, 1) = " " Or Mid(Cells(i, "A"), n - 2, 1) = "(" Then '10分以下の場合
Cells(i, "C") = "0:" & Mid(Cells(i, "A"), n - 1, 4)
Else '10分以上
Cells(i, "C") = "0:" & Mid(Cells(i, "A"), n - 2, 5)
End If

補足

kkkkkmさん、ありがとうございます。

確かに、"0:"を付加せずに直接しない場合
（Cells(i, "C") = "0:" & Cells(i, "C")が無い場合）

① 12:15 ------> ② 12:15:00
③ 2:15 -------> ④2:15:00

となります。

単純に”0：”を付加したので何事やらの事に至ったのが判りました。

数学的に考えると上記の
①を秒に直すと735
②を秒に直すと44100
③を秒に直すと135
④を秒に直すと8100

両者に関連を見ると
①、②は、③、④の60分の１になります。
これを利用すれば欲しい数値になるのですが
何か上手くコンバートする方法はありますか　？

回答No.1

m5048172715

質問が不明だが、

12分15秒の12:15を、0:12:15にしてから処理すれば？

補足

kkkkkmさん、ありがとうございます。

確かに、"0:"を付加せずに直接しない場合
（Cells(i, "C") = "0:" & Cells(i, "C")が無い場合）

① 12:15 ------> ② 12:15:00
③ 2:15 -------> ④2:15:00

となります。

単純に”0：”を付加したので何事やらの事に至ったのが判りました。

数学的に考えると上記の
①を秒に直すと735
②を秒に直すと44100
③を秒に直すと135
④を秒に直すと8100

両者に関連を見ると
①、②は、③、④の60分の１になります。
これを利用すれば欲しい数値になるのですが
何か上手くコンバートする方法はありますか　？

-------------------
セル内の文字列には、
「分：秒」と「時：分：秒」が混在するので
最初から統一した「時：分：秒」フォーマットで処理はできません。