VBAで複数の行のナンバーを取得し、その行の列の値を参照して値を入れる

質問

下記のことをしたいのですが、調べてもわかりません。
どなたか教えてください。
よろしくお願いします。

内容：
エクセルシートのA列の値が11である行ナンバーを取得（複数ある可能性があります）する。
その行のE列（5列目）の値と他のテーブルの値を参照して所定の値をA列の値が11である行のE列（5列目）に返す。
以下のVBAを書いてみましたがうまくいきません。

-----------------------------------------------------------------
Dim ROWCOUNT As Integer
Dim row_array() As Variant
Dim i As Integer

'全行数を取得
ROWCOUNT=Worksheets("sheet1").Range("A1",Range("A65536").End(xlUp)).Count

For i = 1 To ROWCOUNT
If Cells(i, 1).Value = 11 Then
Cells(i,1).Offset(0,5).Value=WorksheetFunction.VLookup(Cells(i, 1).Offset(0, 5).Value, Worksheets("sheet2").Range("A1:H1786"), 3, False)
End If
Next I

回答No.2

pulsa · ベストアンサー

そうですね
俺もFindでやると思うけど、あえてループでやってみますか

　　Dim iCnt As Long
　　Dim iMax As Long
　　Dim jCnt As Long
　　Dim jMax As Long
　　Dim flgOK As Boolean

　　iMax = Sheets("Sheet1").Range("A1").CurrentRegion.Rows.Count　　'Sheet1データ量
　　jMax = Sheets("Sheet2").Range("A1").CurrentRegion.Rows.Count　　'Sheet2データ量

　　For iCnt = 1 To iMax　　 'Sheet1ループ
　　　　If Sheets("Sheet1").Cells(iCnt, 1).Value = 11 Then 'Sheet1のA列が"11"の時
　　　　　　flgOK = False '初期化
　　　　　　For jCnt = 1 To jMax　　'Sheet2ループ
　　　　　　　　If Sheets("Sheet1").Cells(iCnt, 5) = Sheets("Sheet2").Cells(jCnt, 1) Then 'Sheet1E列の値とSheet2A列の値が一致した時
　　　　　　　　　　Sheets("Sheet1").Cells(iCnt, 5) = Sheets("Sheet2").Cells(jCnt, 3)　　 'Sheet1E列にSheet2のC列の値を入れる
　　　　　　　　　　flgOK = True　　'見つけた印
　　　　　　　　　　Exit For　　'見つかったら以下不要
　　　　　　　　End If
　　　　　　Next jCnt
　　　　　　If flgOK = False Then　’見つからなかった時
　　　　　　　　MsgBox """" & Sheets("Sheet1").Cells(1, 5) & """" & "　は　Sheet2　に無いねぇ", 64, "残念なお知らせ"
　　　　　　End If
　　　　End If
　　Next iCnt

.Offset(0, 5).Value　はコードが長くなるのでCells(iCnt, 5)に変更
VLookup(…("A1:H1786"), 3, False)から、該当E列の値と見比べる列をSheet2のA列と判断
また、入れ替える値をSheet2のC列と判断

CurrentRegionを使用しているので、レコード数65536以上にも対応可能だが、データに空行があると空行までしかループしないので注意
逆に考えて、同一シート内で空行で区切った複数レコードの検索が出来る

お好きな方をどうぞ

お礼

pulsaさん

ご返答まことにありがとうございます。
また、ご返答が遅くなりすみませんでした。

見つからなかったときの表示まで示してくれてありがたいです。
ご指摘のとおり、findのほうが使いやすいのかもしれませんが、非常に参考になりました。

Sheet1E列の値とSheet2A列の値のデータ型が違うようなので「" "はSheet2に無いねぇ」が出てしまっていますが、なんとかやってみようと思います。
大変ありがとうございました。

回答No.4

pulsa

>『Option Compare Text』
>↑↑
>textを比較するということですか。
どちらかと言うと
Text『で』比較するってことですね
文字コードとしては"Ａ"（全角）　と"A"（半角）は別物ですが、コレを同じとみなす設定です

Like 演算子　のヘルプ辺りにヒントがあります

お礼

pulsaさん

遅くなりましたが、ご教授ありがとうございます。

回答No.3

pulsa

では、も少し
ネットの検索などと同じように、どの程度を『マッチした』と言うかは、多少の幅があります
示したコードは厳密に数値型の『11』を探してますが、
お気付きの『型』による違いを吸収するには、『=』の両方をCStrで囲む事で克服できます
スペースが前後にある時は、両方をTrim
『*・?・#』なんかを使うときはLike演算子を用いるのはご存知でしょうが、さらに日本語独特の『ア（全角）』と『ア（半角）』を一致とみなしたい時もあるハズです
そんなときは
モジュールの一番上に『Option Compare Text』と書きます
するとどうでしょう？
なんと『ア（全角）=ア（半角）』となったではありませんか（某リフォーム番組風）
しかも『Ａ（全角）=A（半角）=ａ（全角）=a（半角）』です
モジュール全体に反映するので、使い所を考える必要がありますが、定型で入力されていないデータを扱う時は重宝します

Findにもこの設定があるので、その際は単独で使用できます
マクロの記憶を取りながら、検索(Ctrl+F)で『全半角を～』などの設定をあれこれ試してみれば、どのステータスがそれなのか探すのは簡単です

あと、ひじょーに単純なミスをされてます
A列から見て同じ行のE列を取得する為　Offset(0, 5) とされてますが、同行B列は　Offset(0, 1)　ですよね
するとE列は…
実はそこを直せば、示されたコードは動作します

うまくいかないとの事だったので、俺もはなぢまん5さんのコードを試さず先入観のみでエラそうに能書き垂れました
これで飯食ってる人間としては、恥ずべき行為ですね
すいません

改心せねば！
んじゃ。

お礼

pulsaさん

ご教授ありがとうございます。
>『=』の両方をCStrで囲む
↑↑
なるほど、どちらの値もstrings型にするということですね。

『Option Compare Text』
↑↑
textを比較するということですか。
勉強してみます。

>するとE列は…
↑↑
すみません。ご指摘の通りです。

>うまくいかないとの事だったので、俺もはなぢまん5さんのコードを試さず先入観のみでエラそうに能書き垂れました
>これで飯食ってる人間としては、恥ずべき行為ですね
>すいません
↑↑
とんでもない！！
いろいろ教えて頂き、とても感謝しております。
うまくいかないのは私がデータの型を処理していなかったからです。
すみませんでした。

いろいろ教えて頂き、どうもありがとうございました。

回答No.1

redfox63

RangeオブジェクトのFind,FindNextで調べたほうが簡単かと思います

Dim r as Range, fadd as String
Dim ar as range

Set ar = Worksheets("Sheet1").Range("A:A")
with ar
　　' 最初の11を検索
　　set r = ar.Find(11, Lookin:=xlValues)
　　if not r is nothing then
　　　　' 要素が見つかった場合
　　　　fadd = r.address(0,0)
　　　　do
　　　　　　r.Offset(0,5).value = Worksheets("Sheet2"). r.Offset(0,5).value, _
　　　　　　　　Worksheets("Sheet2").Range("A1:H1786"),3,False )
　　　　　　' 次を検索
　　　　　　set r = .FindNext(r)
　　　　' 11が見つからないか 最初に見つかったセルなら終了
　　　　loop while ( not r is nothing ) and ( fadd <> r.address(0,0) )
　　end if
End With
といった具合です

お礼

redfox63さん

ご返答、まことにありがとうございます。
また、ご返答が遅くなりましてすみません。

なるほど、find,findnextで調べるほうがコードを短くして、適切にできるということですね。

どのようにやるべきかわかったような気がしますが、

do
r.Offset(0,5).value = Worksheets("Sheet2"). r.Offset(0,5).value, _
Worksheets("Sheet2").Range("A1:H1786"),3,False )

のところがよくわかりません。
（私の質問の仕方が悪かったせいだと思いますが。）
私はvlookupを使ってsheet2のA列から同じ値を探せばいいのかと考えていましたが、redfox63さんもそのようにお考えということでしょうか？
もしよろしければ、ここのところについてもお教えいただけるとありがたいです。
よろしくお願いします。