VBAでタイムシリアル値から日付のみ抜き出す方法について

質問

VBAでアプリケーションを作っています。
要件
１．yyyy/mm/dd/hh:mm形式のＡ列があり、その列をもとにＢ列に日の部分だけ表示したい。たとえば、9/22日の場合は22という風に数値型で表示。
２．Ｂ列の値を使ってソートするため、タイムシリアル値の表示形式をNumberFormatなどで変換するのではなく、ＶＢＡの処理でタイムシリアル値から日を求めたい。
３．Ａ列の行数は毎月異なる。

要件３の部分は、while文でＡ列が空白になるまでループさせることで解決しました。（数千行あるので、もう少し効率化できたらいいのですが）

今、要件２の部分でつまづいています。
はじめはＡ列をコピーしてＢ列にペーストしたのですが、それでは当然実データがタイムシリアル値のためソートするとおかしくなりました。

なのでＡ列から日のみを抜き出してＢ列に入れる処理を作成中なのですが、
Dim cnt As Integer
Dim day1 As Date
Dim day2 As Integer
cnt = 3
Do While ActiveCell.Value <> ""
Range("A" & cnt).Select
day1 = day(Selection.Value)
day2 = CInt(day1)
Range("B" & cnt).Value = day2
cnt = cnt + 1
Loop
これで元データ（8/1 0:00～8/31 23:55まで昇順）で試すと、
8/1は普通に1が入るのですが、8/2以降は1900/1/2といった
形式のデータがB列に入ってしまいます。

初心者で申し訳ないのですが、どうしたらいいのかわからないので教えてください。
また、要件３はループだと8000回以上繰り返すことになるのですが、
もっと効率のいい方法があれば知りたいです。

回答No.4

myRange

回答は出てますが、
折角質問者のコードがあるのでそれでスピードアップを図る方法を。

提示のコードの問題点は、セルをSelectしてる点にあります。
これは一回一回マウスでセルをクリックしているイメージですね。
で、Selectしないようにします。

'---------------------------------------------　
Sub Test()

　Dim Stime
　Stime = Time

　Dim Cnt As Long
　Dim Day2 As Integer

　Range("B:B").NumberFormatLocal = "##"　　'●Ｂ列表示形式、数値

　Cnt = 3
　Do While Range("A" & Cnt).Value <> ""
　　　Day2 = Day(Range("A" & Cnt).Value)
　　　Range("B" & Cnt).Value = Day2
　　　Cnt = Cnt + 1
　Loop

　MsgBox Stime & vbLf & Time
End Sub
'----------------------------------------------

上記のようにSelectしなければ数千行でも１～２秒程度でしょう。

それから、今回のように変数でセルを参照する場合は
　　Range("A" & cnt).Value
より
　　Cells(cnt, 1).Value
　　Cells(cnt, "A").Value
の方がいいのかな、と思います。

●セルやシートのSelectは必要最小限にすること！
以上です。

回答No.3

imogasi

>8/2以降は1900/1/2といった
形式のデータがB列に入ってしまいます。
こんなのＶＢＡの質問でなく、エクセルの書式の基礎事項ですよ。
ＶＢＡで結果をセルにセットするとき、数値で扱いたい（見たい）ときは、数値の書式にするのは常識。
日付書式を扱っていると、日付データ（当然書式は日付になっている）付近のセルの結果も、自動で書式が日付扱いになってしまう事は良く経験する。その場合
手動で　または
ＶＢＡで
前もって、または気づいて事後に数値書式に設定すれば仕舞い。
また日付というと、年月日か年月日＋時刻をいう。質問文では、（年月日の）「日」と表現しないとおかしい部分がある。
第２
＞値を使ってソートするため
＞表示形式をNumberFormatなどで変換するのではなく
（Ａ）エクセルでも、（他のアプリでも、基本的には）、ソートはセルの「値」で並べ替えるのだ。書式ではない。書く必要なし。
（Ｂ）日付は、エクセルでは、セルの値を、日付シリアル値という正整数で持っている。そのままソートすれば整数のソートになり、自ずと日付順になるので、なんら考える必要なし。
＞ＶＢＡの処理でタイムシリアル値から日を求めたい。
ごたごた書いているが、ＶＢＡには、Ｄａｙ関数があるので、日は
Ｄａｙ（Ｒａｎｇｅ（”Ａ１”））のように取れる。
ーー
ＶＢＡ云々のまえに、エクセルの、書式、日付シリアル値、セルの値とセルの表示形式、その他（コメントなど）の区別の認識など勉強のこと。エクセルの知識があって、その後エクセルＶＢＡがあることを思い起こすこと。
ーーー
＞要件３の部分は、while文でＡ列が空白になるまでループさせることで解決しました
これもエクセルの場合は、最終行番号を求めＦｏｒＮｅｘｔでの繰り返しの方法をお勧めする。

回答No.2

n-jun

n-junです。

＞8/1は普通に1が入るのですが、8/2以降は1900/1/2といった
＞形式のデータがB列に入ってしまいます。
事前にＢ列の「セルの書式設定」が日付になっているため、上記のような
表示になっていると思います。

回答No.1

n-jun

Sub try()
　With Range("A3", Cells(Rows.Count, 1).End(xlUp)).Offset(, 1)
　　　　　.Formula = "=DAY(A3)"
　　　　　.Value = .Value
　　　　　.NumberFormatLocal = "G/標準"
　End With
End Sub

みたいな感じでしょうか？
⇒Ａ列はＡ３以下にデータがあるとしてますので。