みんなの「教えて(疑問・質問)」にみんなで「答える」Q&Aコミュニティ

こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

解決済みの質問

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

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

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

投稿日時 - 2008-06-14 01:12:22

QNo.4098992

困ってます

質問者が選んだベストアンサー

質問の表現に矛盾があるのでは。
Sheet1とSheet2は行とその内容で
(1)前月分の同じ表
(2)VLOOKUPを使う
(1)はイメージ的に同じというだけか。
全く同じなら=SHett2!A3
などが出来るのでは?
ーーーー
またマクロを使わなくても下記で関数で出来ると思う。
(1)まずSheet1の空き列に上行から連番を振る
(2)勤務地列でソートする。
空白と0の行は塊る。
(3)その空白の最初行にVLOOKUPの式を入れて0の行の塊文に式を複写
(4)0の行についても最初行にVLOOKUPを入れ、0の行に式を複写
(5)(1)の連番でソート(順序を元に戻す。連番列は削除。
ーーーーーー
マクロなら
例データ
質問の通りA1:B11
VBAコード
標準モジュールに
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
1の行だけ式が入る。
式は値複写で消すかどうか。
ーーー
VBAでやるのは凝りすぎと思うが。
またこの質問は規約違反の課題の丸投げですよ。

投稿日時 - 2008-06-14 07:57:50

お礼

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

投稿日時 - 2008-06-15 23:15:55

ANo.2

このQ&Aは役に立ちましたか?

0人が「このQ&Aが役に立った」と投票しています

回答(4)

ANo.4

最短コードにトライしてみました。動的名前を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

投稿日時 - 2008-06-14 10:18:52

お礼

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

投稿日時 - 2008-06-15 23:55:35

ANo.3

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

投稿日時 - 2008-06-14 09:18:37

ANo.1

コードを書いて教えて!、では無いね?
手動で、VLOOKUPの式は書けますね?

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

投稿日時 - 2008-06-14 03:10:27

あなたにオススメの質問