sheet1の●列のセルをsheet2の◆列で検索し、ヒットがあればsheet1の同行数列後の数値をsheet2のヒット行数列後のセルに入力する…というマクロを作っています。
sheet2でのヒットが複数あるため、findとfindnextを利用しているのですが
findのみの場合は動くのに、findnextについては全く反応しないマクロが出来てしまいました。
不格好な記述ですが、原因がお解りになる方がいらっしゃいましたらご教授頂ければ幸いです。
findとfindnextのルールは調べた限りでは守っているはずなのですが、原因がわからず困っております。
Sub test()
Application.ScreenUpdating = False
Dim i As Long
Dim ii As Long
Dim p As Long
Worksheets(1).Activate
With ActiveSheet
x = .UsedRange.Cells(.UsedRange.Count).Row
End With
Worksheets(2).Activate
With ActiveSheet
k = .UsedRange.Cells(.UsedRange.Count).Row
End With
For i = 1 To x
Dim lngYLine As Long
Dim intXLine As Integer
Dim Obj As Object
Dim fnd As Range
Dim fnd2 As Range
Dim adr As String
Set Obj = ActiveWorkbook.Worksheets(2).Range("I1", Cells(k, 9)).Find(What:=ActiveWorkbook.Worksheets(1).Cells(i, 1).Value, LookIn:=xlValues, LookAt:=xlPart)
If Obj Is Nothing Then '見つからなかった場合の処理
ActiveWorkbook.Worksheets(1).Cells(i, 1).Interior.ColorIndex = 3
Else '見つかった場合の処理
ActiveWorkbook.Worksheets(1).Cells(i, 1).Interior.ColorIndex = 4
Set fnd = ActiveWorkbook.Worksheets(2).Range("I1", Cells(k, 9)).Find(What:=ActiveWorkbook.Worksheets(1).Cells(i, 1).Value, LookIn:=xlValues, LookAt:=xlPart)
adr = fnd.Address
q = Obj.Row
p = ActiveWorkbook.Worksheets(1).Cells(i, 18).Value
ActiveWorkbook.Worksheets(2).Cells(q, 14) = p
End If
'=======
Do
Set fnd2 = ActiveWorkbook.Worksheets(2).Range("I1", Cells(k, 9)).FindNext(fnd)
If fnd2.Address = adr Then '上書きしたfndのアドレスが最初のfndアドレスと一緒なら終了
Exit Do
Else 'そうでなければ以下の処理を続ける
ActiveWorkbook.Worksheets(1).Cells(i, 1).Interior.ColorIndex = 4
q = Obj.Row
p = ActiveWorkbook.Worksheets(1).Cells(i, 18).Value
ActiveWorkbook.Worksheets(2).Cells(q, 14) = p
End If
Loop
'=======
Next i
Application.ScreenUpdating = True
End Sub
No.3ベストアンサー
- 回答日時:
こんにちは。
もう2名も先に回答がついたので、これで十分なのですが、思うことがあり参加させていただきます。
ふつうは、 範囲.Find ->見つかれば、->範囲.FindNext
範囲を新たに設定することはないので、通常はWith ステートメントでつなぎます。
Set Obj = ActiveWorkbook.Worksheets(2).Range..Find(...
Set fnd = ActiveWorkbook.Worksheets(2).Range..Find(....
Do
Set fnd2 = ActiveWorkbook.Worksheets(2).Range.. FindNext(fnd) '*
Loop
*これは、戻り値をfnd2にしたらつながりませんね。
> Worksheets(1).Activate
> With ActiveSheet
> x = .UsedRange.Cells(.UsedRange.Count).Row
> End With
決して、UsedRangeで最後尾を求める方法が悪いわけではないのですが、最後のセルのとり方として、とんでもない数字が入る要素があるのと、これで検索値を求めると、空白値が入り込む要素が高いです。検索データ列も、被検索列もともに、一列のみのようでしたら、Endプロパティで求めたほうがよいです。
また、私は、ループで使う変数は、短めにするということにしています。
わかりにくくしているのは、最初、変数を全体的に有効利用していないからだと思いました。#2さんの回答と、だいたい注目点はおなじだと思っていますが、もしかしたら、解釈が違っているかもしれません。
私は、検索のxlPart の件ですが、xlWholeにして、検索値 &"*" とワイルドカードを使うことがあります。ただ、一般的に文字列の検索に利用し、数値は、xlWholeで、xlPart は使いません。一旦、検索したものを、桁などを処理して正しいか検査してから、検索値として扱います。
こんなふうに直してみました。
'//
Sub Test2()
Dim sh1 As Worksheet
Dim sh2 As Worksheet
Dim o As Object
'' Dim fnd As Range '使うのを辞めました。
Dim Rng As Range
Dim r As Range
Dim k As Long
Dim i As Long
Dim x As Long
Dim FirstAddress As String
Set sh1 = ActiveWorkbook.Worksheets("Sheet1")
Set sh2 = ActiveWorkbook.Worksheets("Sheet2")
With sh2
Set Rng = .Range("I1", .Cells(Rows.Count, "I").End(xlUp))
End With
With sh1
x = .Cells(Rows.Count, 1).End(xlUp).Row
End With
For i = 1 To x
Set r = sh1.Cells(i, 1)
If r.Value <> "" Then
Set o = Rng.Find(What:=r.Value, LookIn:=xlValues, LookAt:=xlPart)
If Not o Is Nothing Then
FirstAddress = o.Address
Do
r.Interior.ColorIndex = 4
sh2.Cells(o.Row, 14).Value = sh1.Cells(i, 18).Value
Set o = Rng.FindNext(o)
Loop While Not o Is Nothing And o.Address <> FirstAddress
Else
r.Interior.ColorIndex = 3
End If
End If
Next
End Sub
詳しくご指摘頂き、ありがとうございます!頂いたマクロで動きました…!
変数がいまいち苦手でついベタ書きをしてしまうのですが、元の記述にも近く大変理解しやすかったです。
ご回答ありがとうございました!
No.2
- 回答日時:
こんにちは
すでに指摘がありますが、うまくいかない原因になりそうなのは、
1)Doループの中で
>Set fnd2 = ActiveWorkbook.Worksheets(2).Range("I1", Cells(k, 9)).FindNext(fnd)
としているため、fndの内容が常に一定(変化しない)なので、fnd2も同じ値を取り続けることになり、対象が複数存在する場合はループを抜けなくなる。
2)検索処理の手順全体が
>If Obj Is Nothing Then
>Else
>End If
>Do
>Loop
という構造になっていますが、見つからない場合(Obj is Nothing)でも、後段のDoループの処理は実行されてしまいます。
・・・といったところでしょうか?
そのほかにも、
3)検索値(Worksheets(1)のA列?)の値に空白がある場合にも空白値で検索するけれど・・?
4)検索方法がxlPartなので、違う値でも複数回ヒットする可能性があり、最後にヒットした時の値で上書きされる。
(データの性質上、3)、4)のようなことは起こり得ないのかもしれませんが)
・・・などが気になりますが、これで結果が異なる可能性があるものの、処理が止まることは無いと思います。
また、処理内容とは関係がありませんが、記述方法として、属性値を利用するのに同じドット記法を何度も繰り返していたり、あるいは、同じ処理を複数回繰り返し記述しているように見えますが、変数を利用したり、処理順序を工夫したりすることで、もう少し簡潔に記載できそうな気がします。
ちゃんと確認していないので、行や列がずれてしまっているかも知れませんが、以下のような雰囲気でも処理ができるのではないかと思います。
※ ご提示のコードにはありませんが、セル値が空白の場合に検索をスキップする処理を勝手に追加しています
Sub test()
Dim sh1 As Worksheet, sh2 As Worksheet
Dim searchRange As Range, fnd As Range
Dim rw As Long, cellColor As Integer
Dim addr As String
' 変数の初期設定
Set sh1 = Worksheets(1)
Set sh2 = Worksheets(2)
Set searchRange = sh2.Cells(1, 9).Resize(sh2.Cells(Rows.Count, 9).End(xlUp).Row)
' 元シートの各行でループ
For rw = 1 To sh1.Cells(Rows.Count, 1).End(xlUp).Row
cellColor = 4
' 空セルの場合はスキップ
If sh1.Cells(rw, 1) <> Empty Then
Set fnd = searchRange.Find(What:=sh1.Cells(rw, 1).Value, LookIn:=xlValues, LookAt:=xlPart)
' 検索できない場合の処理
If fnd Is Nothing Then
cellColor = 3
' 検索できた場合の処理
Else
addr = fnd.Address
Do
sh2.Cells(fnd.Row, 14).Value = sh1.Cells(rw, 18).Value
Set fnd = searchRange.FindNext(fnd)
Loop While Not fnd Is Nothing And fnd.Address <> addr
End If
sh1.Cells(rw, 1).Interior.ColorIndex = cellColor
End If
Next rw
End Sub
詳しくご指摘頂き、ありがとうございます!
頂いた記述で動きました!
>fndの内容が常に一定(変化しない)なので、fnd2も同じ値を取り続けることになり、対象が複数存在する場合はループを抜けなくなる。
こちらが特にわかりやすかったです。
fnd=fndのような同じ変数を使う数式の理解が苦手で避けてしまっていたのですが、それが原因となる場合もあるのですね…。感謝いたします。
No.1
- 回答日時:
ヘルプより
FindNext メソッド
Find メソッドによって開始された検索を継続します。
--------
とあります。
なら原因は多分
>Set fnd = ActiveWorkbook.Worksheets(2).Range("I1", Cells(k, 9)).Find(What:=ActiveWorkbook.Worksheets(1).Cells(i, 1).Value, LookIn:=xlValues, LookAt:=xlPart)
>Set fnd2 = ActiveWorkbook.Worksheets(2).Range("I1", Cells(k, 9)).FindNext(fnd)
受け取る変数が違うからかも知れません。
(シートの状況がわからないので検証してませんが)
もし既に確認済みならほっといて良いですよ。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Visual Basic(VBA) vbaを早くしたい 5 2022/09/09 10:58
- Visual Basic(VBA) VBA 別ブックからの転記の高速化について VBA 別ブックからの転記の高速化についてご教授下さい。 19 2022/07/26 13:07
- Visual Basic(VBA) ユーザーフォームに2つのコンボボックス銀行名「ConboBox1」支店名を「ConboBox2」とし 4 2022/08/03 17:34
- Visual Basic(VBA) 前回ご教授いただいたコードに覚えたてのループ処理で品名りんごAから順に20回for nextでループ 7 2023/01/13 22:01
- Excel(エクセル) VBAの指示の内容 昨日こちらでご教示頂いたのですが初心者な為、一つ一つの指示が何をやっているのかわ 2 2022/10/25 18:08
- Excel(エクセル) なぜExit Subがあるのかわかりません 4 2023/02/19 12:34
- Visual Basic(VBA) Sheet3から2つの条件でオートフィルターで抽出した個数をSheet2へ入力するマクロで、一つ目の 4 2023/01/12 23:40
- Visual Basic(VBA) VBA処理追加 こちらでご教示頂いたのですが回答完了させてしまいましたのでこちらからまた質問させてく 2 2022/10/27 09:57
- Visual Basic(VBA) VBAコードが作動せず、どこに問題があるのか教えて下さい。 3 2023/06/13 13:20
- Visual Basic(VBA) 【前回の続き続きです、ご教示ください】VBAの記述方法がわかりません。 2 2022/08/24 20:49
このQ&Aを見た人はこんなQ&Aも見ています
-
新NISA制度は今までと何が変わる?非課税枠の拡大や投資対象の変更などを解説!
少額から投資を行う人のための非課税制度であるNISAが、2024年に改正される。おすすめの銘柄や投資額の目安について教えてもらった。
-
FindNextがうまくいかない
Visual Basic(VBA)
-
VBAで保存しないで閉じると空のBookが残る
Excel(エクセル)
-
VBAのFind関数で結合セルを検索するとヒットしない
Visual Basic(VBA)
-
-
4
【excelVBA】Findメソッドで検索対象を複数列
Excel(エクセル)
-
5
複数の条件に合う行番号を取得するには
その他(Microsoft Office)
-
6
エクセルで複数列の検索をマクロで行いたい
Excel(エクセル)
-
7
EXCEL VBAで全選択範囲の解除
Excel(エクセル)
-
8
VBAでブックを非表示で開いて処理して閉じる方法
Excel(エクセル)
-
9
VBAでループ内で使う変数名を可変にできないか。
Visual Basic(VBA)
-
10
エクセルのラベルの値(文字列)を垂直方向で中央揃えにするには?
Excel(エクセル)
-
11
VBAでCSVファイルが使用中かどうかの確認
Visual Basic(VBA)
-
12
EXCEL VBA セルに既に入力されている文字に文字を追加する
Excel(エクセル)
-
13
「Columns(A:C")」の列文字を数字にして表記したい"
Excel(エクセル)
-
14
Application.ScreenUpdating = Falseが効きません
Visual Basic(VBA)
-
15
エクセルでエラーが出て困っています。
Excel(エクセル)
-
16
コンボボックスにリストが表示されません・・・
Excel(エクセル)
-
17
VBA シートのボタン名を変更したい
Visual Basic(VBA)
-
18
エクセルVBAで5行目からオートフィルタモードに設定したいたい
Excel(エクセル)
-
19
【EXCEL VBA】Range(A:A").Find(What:="キーワード")の1行目について"
Visual Basic(VBA)
-
20
split関数で区切り文字がない場合
Visual Basic(VBA)
関連するカテゴリからQ&Aを探す
おすすめ情報
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
「ご処理進めて頂きますようお...
-
エクセルで、日付を入力すると...
-
【Excel】特定の文字を含むセル...
-
メルカリのメルカードで買い物...
-
VBAでループ内で使う変数名を可...
-
switch の範囲指定
-
EXCEL VBA マクロ 実行する度に...
-
DoEventsがやはり分からない
-
VBA SaveChanges 上書きされない
-
VBの質問#if 0 then ってどう...
-
C言語 b += a ? 1 : 0; の意味
-
UMLでの例外処理
-
リョウ・・・量?料?
-
月度は何て読みますか?
-
iD
-
findは動くがfindnextがマクロ...
-
VBAでGetAsynckeyStatekのエラー
-
TextBoxに日付を自動的に入れる
-
ビープ音を連続して鳴らす
-
VB.NET Excelを読み込んでDataT...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
「ご処理進めて頂きますようお...
-
メルカリのメルカードで買い物...
-
エクセルで、日付を入力すると...
-
VBAでループ内で使う変数名を可...
-
【Excel】特定の文字を含むセル...
-
EXCEL VBA マクロ 実行する度に...
-
DoEventsがやはり分からない
-
UMLでの例外処理
-
月度は何て読みますか?
-
switch の範囲指定
-
VBの質問#if 0 then ってどう...
-
セルの値が0はクリアするマクロ
-
VB.NET Excelを読み込んでDataT...
-
Do~Loopした回数をカウントしたい
-
Loadイベント中にほかのイベン...
-
Select Case文でこのようなこと...
-
findは動くがfindnextがマクロ...
-
緊急です。 知り合いから50kgの...
-
リョウ・・・量?料?
-
理不尽、行き場のないイライラ...
おすすめ情報