回数を指定しないで空白行まで繰り返し処理

セル内にある"/"区切りの年月日を分割するために以下のコードを使用しています。
以前まではこれでよかったのですが、行（データ）が増えたり減ったりするようになりました。
何行でも、最終の空白行まで対応できるようにしたいのですがうまくいきません。
改善できませんでしょうか？
宜しくお願いいたします。

Sub 日付分割()
Dim i As Long, tmp As Variant
For i = 2 To 10
tmp = Split(Cells(i, 14), "/")
Cells(i, 15) = tmp(0)
Cells(i, 16) = tmp(1)
Cells(i, 17) = tmp(2)
Next i
End Sub

No.1 ベストアンサー 回答者： めぐみん_ 回答日時： 2022/02/01 20:02

For i = 2 To 10

↓

For i = 2 To Cells(Rows.Count, 14).End(xlUp).Row

かな？

tmp = Split(Cells(i, 14), "/")
Cells(i, 15) = tmp(0)
Cells(i, 16) = tmp(1)
Cells(i, 17) = tmp(2)

↓

tmp = Split(Cells(i, 14), "/")
Cells(i, 15).Resize(, UBound(tmp) + 1).Value = tmp

でもいけるかも？

この回答へのお礼

ばっちりうまくいきました❗
ありがとうございました❗

お礼日時：2022/02/03 00:01

No.3 回答者： Qchan1962 回答日時： 2022/02/02 15:26

こんにちは

横から失礼します
年月日を考慮せず、文字列としてSplitで処理。なんとなく以前私が回答したような気がして・・＃２様のアドバイスも納得するところです。

＞最終の空白行まで対応できるようにしたい
End(xlDown)が良いのかEnd(xlUp)が良いのか判断に困るところですね
Splitで良いのか、Dateにするべきか、、

End(xlDown)で2通りサンプル

Sub 日付分割()
Dim r As Range, stPoint As Range
Set stPoint = Cells(2, 14)　'スタートセル
For Each r In Range(stPoint, stPoint.End(xlDown))
r.Offset(, 1).Resize(, 3).Value = Split(r.Text, "/")
Next r
End Sub
日付でない"/"区切りの文字列でも分けられる
（区切り文字が無い場合には文字全体が1番目のセルに出力される）

Sub 日付分割1()
Dim r As Range, stPoint As Range
Set stPoint = Cells(2, 14)
For Each r In Range(stPoint, stPoint.End(xlDown))
If IsDate(r) Then
r.Offset(, 1).Resize(, 3).Value = Array(Year(r), Month(r), Day(r))
Else
r.Offset(, 1) = "not Date"
End If
Next r
End Sub

日付限定処理になります。
If IsDate(r) Thenは日付に出来ないデータであった場合の型エラー対策です
ループの仕方をFor Eachとしましたが分かりますでしょうか？

この回答へのお礼

ありがとうございます！
とても参考になりました。

お礼日時：2022/02/02 23:59

No.2 回答者： naktak 回答日時： 2022/02/02 11:55

VBAなど利用しなくてもできます。

【パターン１】
　=IF(ISBLANK(N2),"",YEAR(N2))
　=IF(ISBLANK(N2),"",MONTH(N2))
　=IF(ISBLANK(N2),"",DAY(N2))
　として
　月、日のセルの書式設定を00とする。

【パターン２】
　年、月、日の数式をすべて=N2とする。
　セルの書式設定で
　yyyy;;
　m;;
　d;;
　とする。

以上をすべての行に設定しておけばよくないですか？

それでもVBAでやりたい場合はすでに回答がある通り、対象列の最終行を求めればいいかと思います。
ただコードとしてはSplit()で文字扱いで制御するのではなく、年、月、日をそれぞれ求めればいいかと思います。
視覚的に見える表現とは別に、数値の値を文字列として設定することに意味はないでしょう。
Sub 日付分割()
Dim dateValue As Date
dateValue = Cells(2, 14).value
If dateValue = Empty Then
Cells(2, 15).value = Empty
Cells(2, 16).value = Empty
Cells(2, 17).value = Empty
Exit Sub
End If

Cells(2, 15).value = Year(dateValue)
Cells(2, 16).value = Month(dateValue)
Cells(2, 17).value = Day(dateValue)
End Sub

月と日のセルの書式設定で00と指定すればいいです。
左寄せがいいなら配置の横位置を左詰めにすればいいです。

この回答へのお礼

ありがとうございます！
関数でもいけますね、勉強になりました。

お礼日時：2022/02/03 00:00