VBA「型が一致しません」とエラーが出ます

質問

下記記事の関連質問です。

Excel 文字列抜き出しについて
https://okwave.jp/qa/q9979633.html

記事を参考に複数行にマッチするように下記のようにコードを考えましたが
msplit(mRng, " ", iii)で「型が一致しません」とエラーが出ます。
なぜでしょうか？

テスト用のA4セル
1 01 45124422 ミント 09/01～03/01 108 98 01/05～02/01

Option Explicit
Sub test()

Dim msplit As Variant
Dim buf As String, i As Long, ii As Long, iii As Long, cnt As Long
Dim mRng As Range

For ii = 4 To Cells(Rows.Count, 1).End(xlUp).Row
buf = Cells(ii, "A")
For i = 1 To Len(Cells(ii, "A"))
If Mid(buf, i, 1) = " " Then cnt = cnt + 1
Next

For iii = 1 To cnt
Set mRng = Cells(ii, "A")
’Stop
Cells(iii + 1, ii) = msplit(mRng, " ", iii)
Next
Next
Set mRng = Nothing
End Sub

Function msplit(ByRef mRng As Range, ByVal Spstr As String, ByVal num As Long) As Variant
msplit = Split(mRng, Spstr)(num - 1)
End Function

回答No.6

kkkkkm · ベストアンサー

あと

Cells(ii, iii + 1)
と
Cells(iii + 1, ii)
が変わっているのはご自身で変更してください。
私は質問のコードを触って回答してますので。

お礼

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

>私は質問のコードを触って回答してますので。

最初のコードで不備が合ったのが原因です。
申し訳ないです。

時間のmm:ss部分がhh:mm:ssと成ったのでとても便利になりました。

回答No.5

kkkkkm

私のマクロと他の方のマクロは基本的に違います。

元の質問は、必要な部分を取り出したいということでしたので、私のマクロは、部分ごとに取り出せるように作成しています。
他の方のマクロは、条件とは関係なく全ての部分を取り出すようにしています。

ですので、全て一気に取り出したいのでしたらそのようなコードを利用するのがいいのではないでしょうか。

回答No.4

kkkkkm

> セル分離された文字列（数値）が意図したものになりません。

全て文字列にしたいのでしたら

Set mRng = Cells(ii, "A")
の下で
Cells(2, ii).Resize(cnt + 1, 1).NumberFormatLocal = "@"
として以降の
.NumberFormatLocalの部分をなくしたらいいのではないでしょうか。

回答No.3

kkkkkm

B4の0:04は4秒だとしたらそのままではB4では4分になっていると思います。
文字列が時刻の場合、分岐をしていた方がいいと思います。

'Stop
Dim temp As Variant
temp = msplit(mRng, " ", iii)
With Cells(iii + 1, ii)
'↓日付もしくは時刻かどうか
If IsDate(temp) Then
'↓時刻データかどうかの判断
If CDate(temp) < 1 Then
'時刻データの場合
If Len(temp) - Len(Replace(temp, ":", "")) = 1 Then ':が1個
.NumberFormatLocal = "mm:ss"
.Value = "0:" & temp
ElseIf Len(temp) - Len(Replace(temp, ":", "")) = 2 Then ':が2個
.NumberFormatLocal = "hh:mm:ss"
.Value = temp
End If
Else
'日付データの場合 日付データ的なものがない場合不要
.NumberFormatLocal = "mm/dd"
.Value = temp
End If
Else
'通常の文字列や数値の場合
.Value = temp
End If
End With

'時刻データかどうか判断する【IsDate関数応用】
'↓https://vba-create.jp/vba-isdate-istime/

あと、文字列の先頭と最後にスペースがあったりなかったりするデータの場合、書き込むセルの位置がずれますので

buf = Trim(Cells(ii, "A"))

Functionの方で
msplit = Split(Trim(mRng), Spstr)(num - 1)

としていてもいいかもしれません。

お礼

追加のアドバイス感謝します。

buf = Trim(Cells(ii, "A"))　に変更しました。

kkkkkmさんのマクロでは、数値（時間）がそのまま数値にとしてセルに分離されるのは理解していました。
元の「Excel 文字列抜き出しについて」の質問でimogashiさんの書き込みに有るマクロを勉強のために
利用したら数値（時間）が文字列としてセルに分離されるのでこれはこれで使い分けが出来そうだと思った次第です。
（最下部の「Sub ②半角スペース毎に文字列分割_分割された数値は文字列となる()」がそれです。）

＞文字列が時刻の場合、分岐をしていた方がいいと思います。
上記を受けて下記のようにマクロを修正しましたが
セル分離された文字列（数値）が意図したものになりません。
何処が変でしょうか　？

どんなふうになるか？
参考画像を添付します。

'分割された数値は、数値のママとなる
Sub ①_2半角スペース毎に文字列分割_分割された数値は数値のママとなる()

Dim buf As String, i As Long, ii As Long, iii As Long, cnt As Long
Dim mRng As Range
Dim temp As Variant

For ii = 2 To Cells(Rows.Count, 1).End(xlUp).Row
'文字列の先頭と最後にスペースがあったりなかったりするデータの場合、書き込むセルの位置がずれますので
'Trim:引数stringで指定した文字列から先頭のスペースと、末尾のスペースを削除。
buf = Trim(Cells(ii, "A"))

For i = 1 To Len(buf)
If Mid(buf, i, 1) = " " Then cnt = cnt + 1
Next

For iii = 1 To cnt + 1
Set mRng = Cells(ii, "A")

temp = msplit(mRng, " ", iii)
With Cells(iii + 1, ii)
'↓日付もしくは時刻かどうか
If IsDate(temp) Then
'↓時刻データかどうかの判断
If CDate(temp) < 1 Then
'時刻データの場合
If Len(temp) - Len(Replace(temp, ":", "")) = 1 Then ':が1個
.NumberFormatLocal = "mm:ss"
.Value = "0:" & temp
ElseIf Len(temp) - Len(Replace(temp, ":", "")) = 2 Then ':が2個
.NumberFormatLocal = "hh:mm:ss"
.Value = temp
End If
Else
'日付データの場合 日付データ的なものがない場合不要
.NumberFormatLocal = "mm/dd"
.Value = temp
End If
Else
'通常の文字列や数値の場合
.Value = temp
End If
End With
Next
cnt = 0
Next

'列幅／高さを自動調整する
Columns("A:K").AutoFit

Set mRng = Nothing

End Sub

Function msplit(ByRef mRng As Range, ByVal Spstr As String, ByVal num As Long) As Variant
msplit = Split(mRng, Spstr)(num - 1)
End Function

'----------------- 以下 imogashiさんのマクロ改版
'分割された数値は、文字列となる
Sub ②半角スペース毎に文字列分割_分割された数値は文字列となる()

Dim p As Variant
Dim i As Long

For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row
p = Split(Cells(i, "A"), " ")

'半角スペース区切り文字の受け皿として第2から第１1列まで指定
'受け皿が少ないなら適宜増加する事（多すぎるのは問題なし）
Range(Cells(i, 2), Cells(i, 11)) = p
Next

'受け皿が多すぎると#NAがセルに表示されるので一括置換で空白に変える
'（シート全体の文字列が対象）
Cells.Replace What:="#N/A", Replacement:=""

'列幅／高さを自動調整する
Columns("A:K").AutoFit

End Sub

’---------------------------------　以下参考画像
https://imgur.com/2gYkDwe
[img]https://i.imgur.com/2gYkDwe.jpg[/img]
<blockquote class="imgur-embed-pub" lang="en" data-id="2gYkDwe"><a href="https://imgur.com/2gYkDwe">View post on imgur.com</a></blockquote><script async src="//s.imgur.com/min/embed.js" charset="utf-8"></script>

回答No.2

kkkkkm

あと

> If Mid(buf, i, 1) = " " Then cnt = cnt + 1

のcntはループの中で使う前に0にしておかないと前回のcntにどんどん加算されていきます。

また、cntは以下の方法でも取得できると思います。
cnt = Len(buf) - Len(Replace(buf, " ", ""))

cntはスペースの数を取得していますので
For iii = 1 To cnt
は
For iii = 1 To cnt + 1
にしないと最後が取り出せないと思います。

回答No.1

kkkkkm

Dim msplit As Variant
がいりません。

お礼

kkkkkmさん、アドバイス感謝します。

エラーが除去できないので色々コードを追加していて過程で
又初歩的なミスをやってしまいました。

Dim msplit As Variant　を削除して、
コードを見直して以下のように修正しました。

B列以降の右方向の表示位置を揃えるのに一苦労しましたが
テストDATAを利用して
書き出されたセルの見た目は、上手く出来ているように思えますが
追加のアドバイスあればお願いします。

Option Explicit
Sub test()

Dim buf As String, i As Long, ii As Long, iii As Long, cnt As Long
Dim mRng As Range

For ii = 4 To Cells(Rows.Count, 1).End(xlUp).Row
buf = Cells(ii, "A")

For i = 1 To Len(buf)
If Mid(buf, i, 1) = " " Then cnt = cnt + 1
Next

For iii = 1 To cnt + 1
Set mRng = Cells(ii, "A")
Cells(ii, iii + 1) = msplit(mRng, " ", iii)
Next
cnt = 0
Next

Set mRng = Nothing

End Sub

Function msplit(ByRef mRng As Range, ByVal Spstr As String, ByVal num As Long) As Variant
msplit = Split(mRng, Spstr)(num - 1)
End Function

補足

テストDATAの結果です。

https://imgur.com/obgv7zO.jpeg

https://imgur.com/obgv7zO