エクセルVBA（条件に合うセルを選んで数式に置換）

質問

エクセルのマクロについて質問です。
sheet1 に、下記の様な表があるとします。
sheet2 には前月分の同じ表があり、勤務地の列にブランクや０のものはなく、全て埋まっています。
sheet1のB列が０かブランクのセルには、VLOOKUPで前月のデータを拾いたいと思います。
どのようなマクロを組めば良いでしょう？
実際のデータは500行ぐらいあり、その数は毎月変化します。
アドバイスよろしくお願いいたします。

A列　　　　　B列
《氏名》　　《勤務地》
山田　　　　（空白）
田中　　　　０
中島　　　　（空白）
田上　　　　東京
上田　　　　名古屋
岡田　　　　大阪
岡村　　　　０
村田　　　　大阪
田村　　　　名古屋
林田　　　　（空白）

回答No.2

imogasi · ベストアンサー

質問の表現に矛盾があるのでは。
Ｓｈｅｅｔ１とＳｈｅｅｔ２は行とその内容で
（１）前月分の同じ表
（２）ＶＬＯＯＫＵＰを使う
（１）はイメージ的に同じというだけか。
全く同じなら=SHett2!A3
などが出来るのでは？
ーーーー
またマクロを使わなくても下記で関数で出来ると思う。
（１）まずＳｈｅｅｔ１の空き列に上行から連番を振る
（２）勤務地列でソートする。
空白と０の行は塊る。
（３）その空白の最初行にＶＬＯＯＫＵＰの式を入れて０の行の塊文に式を複写
（４）０の行についても最初行にＶＬＯＯＫＵＰを入れ、０の行に式を複写
（５）（１）の連番でソート（順序を元に戻す。連番列は削除。
ーーーーーー
マクロなら
例データ
質問の通りＡ１：Ｂ１１
ＶＢＡコード
標準モジュールに
Sub test01()
Dim sh1 As Worksheet
Dim sh2 As Worksheet
Set sh1 = Worksheets("Sheet1")
Set sh2 = Worksheets("Sheet2")
d1 = sh1.Range("a65536").End(xlUp).Row
d2 = sh2.Range("a65536").End(xlUp).Row
For i = 2 To d2
If sh1.Cells(i, "B") = "" Or sh1.Cells(i, "B") = 0 Then
sh1.Cells(i, "B").Formula = "=VLookup(A" & i & ",Sheet2!A1:B" & d2 & ",2,FALSE)"
End If
Next i
End Sub
ーーー
実行後
《氏名》 《勤務地》
山田 福岡 1
田中 岡山 1
中島 広島 1
田上 東京
上田 名古屋
岡田 大阪
岡村 岐阜 1
村田 大阪
田村 名古屋
林田 静岡 1
１の行だけ式が入る。
式は値複写で消すかどうか。
ーーー
ＶＢＡでやるのは凝りすぎと思うが。
またこの質問は規約違反の課題の丸投げですよ。

お礼

ありがとうございます！うまく行きました！
おっしゃるとおり、これだけの作業ならVBAでやる必要はないのですが、一連の作業の中の一部です。
VBA初心者なもので、基本的にはマクロの記録をした後いろいろ調べて試行錯誤する、という作業をしていました。
途中、どうしても行き詰ってしまったので質問させていただきました。
次回またわからないことがありましたら、もっと勉強した上で、どこでつまづいたのかを明確に質問するよう心がけます。
OKWaveも初めてだったので、質問内容だけでなく、サイトの利用に関するマナーについても教えていただいたことにとても感謝します！

回答No.4

mitarashi

最短コードにトライしてみました。動的名前をSheet2の元表の範囲につけて下さい。（反則？）
'名前：表　=OFFSET(Sheet2!$A$2,0,0,COUNTA(Sheet2!$A:$A)-1,2)
Sub test()
Dim myCell As Range
For Each myCell In Worksheets("Sheet1").Range("a1").CurrentRegion.Columns(2).Cells
If myCell.Value = 0 Or myCell.Value = "" Then myCell.FormulaR1C1 = "=VLOOKUP(RC[-1],表,2,FALSE)"
Next
End Sub

お礼

ありがとうございます！
表に名前をつけなくても十分短いコードでできました！

回答No.3

keirika

Sub Sample()

Dim rngTougetu As Range
Dim rngZengetu As Range
Dim i As Long

Set rngTougetu = Sheet1.Range("a1").CurrentRegion
Set rngZengetu = Sheet2.Range("a1").CurrentRegion

For i = 2 To rngTougetu.Rows.Count
Select Case rngTougetu.Cells(i, 2)
Case 0, ""
rngTougetu.Cells(i, 2) = _
Application.VLookup(rngTougetu(i, 1), rngZengetu, 2, 0)
End Select
Next
End Sub

回答No.1

bin-chan

コードを書いて教えて！、では無いね？
手動で、ＶＬＯＯＫＵＰの式は書けますね？

考え方
１）行数を把握する
２）Ｂ列のセル単位に、.Text または .Value を評価する。
　　有効な値があれば次行へ。無ければ式設定の対象なので３）へ。
３）式設定の対象であるＢ列のセルの .Formula に対し、セルアドレスを適切に組み立てた[式]を設定する。その上で次行へ。