[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『特定の数字が入力されているセルの位置(列)を知りたい(VBA)』（みみ）
VBA初心者です。
特定の数字が入力されているセルの位置(列)を知りたく、Find(ctrl+f)を使ったVBAを組みました。
しかし、検索条件をデフォルト設定から変更する都合上、マクロを走らせた後に検索しようとするとデフォルト設定に戻す手間が増えてしまいました。
Find以外を使用した方法があれば、教えてください。
(データを移動するマクロを組んでおり、その処理の一部の為、VBAでお願いします。)
▽組んだVBA
Range("D18:AA18").Select '検索範囲選択
Selection.Find(What:=kz, LookAt:=xlWhole).Activate 'ctrl+f
rt = ActiveCell.Column
▽やりたいこと
D18:AA18の中に0～11の数字が入力されており、1は何列目にあるか、11は何列目にあるか？を知りたい。
D18:AA18は2列を結合したセル×12で、数字は重複していない。
並びは数字順ではない(1,2,8,9,3,4,のような)
検索範囲は現状D18:AA18で固定、増減・場所移動などの変動の予定無し。
▽現在
セル位置(列)を知りたい数字をkzに入れて、D18:AA18の範囲で完全一致で検索。
(1や0で10が引っかかると困る為)
rtにセル位置(列)を入れる。
＜ 使用 Excel：Excel2016、使用 OS：Windows10 ＞
>1は何列目にあるか、11は何列目にあるか？を知りたい。
知りたいって rt = ActiveCell.Column とかで分かっていますよね？ （why） 2024/12/25(水) 11:11:09
rt = Application.Match(kz, Rows(18), 0) （マナ） 2024/12/25(水) 11:12:00
かいちゃったので。
見つからないときエラーになりますので、そこは適当に
Sub sample()
Dim targetRng As Range, HitCell As Range
Set targetRng = Range("D18:AA18")
kz = 10
Set HitCell = WorksheetFunction.Index(targetRng, 1, WorksheetFunction.Match(kz, targetRng, 0))
Debug.Print HitCell.Address; HitCell.Column
End Sub
（´・ω・`） 2024/12/25(水) 11:15:56
シート上で使うMatch関数を使ってみてください。
Sub test()
Dim Rng As Range
Dim i As Variant
Dim j As Long
Dim Key As Long
Key = 1
Set Rng = Range("D18:AA18")
i = Application.Match(Key, Rng, 0)
If IsNumeric(i) Then
j = Rng.Cells(i).Column
End If
MsgBox j
End Sub
（まっつわん） 2024/12/25(水) 11:20:21
マナ様
こちら最後のrtの一文を置き換えるということで合っていますでしょうか？
一見では読む解くことができず一つ一つ言語の意味・処理を調べながらになる為、もし先にご回答可能でしたらお願いいたします。
（´・ω・`）様
まっつわん様
まとめてで申し訳ありません。
一見では読む解くことができず一つ一つ言語の意味・処理を調べながらになる為、実行までお時間を頂戴いたします。
もし先に処理の内容について解説可能でしたら、お願いいたします。
（みみ） 2024/12/25(水) 11:25:18
Sub test()
'変数の宣言
Dim Rng As Range '検索範囲
Dim i As Variant 'Match関数の返り値
Dim j As Long '列番号
Dim Key As Long '検索値
'検索値を仮に「1」とする
Key = 1
'検索するセル範囲を変数に代入
Set Rng = Range("D18:AA18")
'match関数で検索
i = Application.Match(Key, Rng, 0)
'もし、Match関数の返り値が数値ならば(見つからなければエラー値が返る）、
If IsNumeric(i) Then
'その時は変数「Rng」のセル範囲のうちの「i番目」のセルの列番号を取得
j = Rng.Cells(i).Column
End If
'メッセージボックスにjの値を表示
MsgBox j
End Sub
分からないところがあれば、聞いてください。
（まっつわん） 2024/12/25(水) 12:07:02
>検索条件がデフォルト設定から変わってしまう為、手間が増えて困っています。
それぐらいを手間ととる事が解りません 終わった後にデフォルト検索をダミーで入れておけば良いのでは 手間、手間、と不満たらたらなので頭が回らないのでは
>Selection.Find(What:=kz, LookAt:=xlWhole).Activate
いきなり検索結果を選択しないで、変数にセットしてから 検索結果を見て振り分ければいいのでは FindやFind(What: とかで検索すれば、エクセルの学校内でも使用例がたくさんあると思います
（why） 2024/12/25(水) 12:24:04
コードの確認をしました。
しっかりと理解できたわけではない処理がある為、はっきりとは言い切れませんが、理想通り動いております。ありがとうございます。
変数をRangeでも宣言することができることを知りました。
オブジェクト変数というのですね。
Sub テスト()
'変数宣言
Dim targetRng As Range, HitCell As Range
Dim kz As Long
'検索範囲を変数に登録(Set 変数名で登録)
Set targetRng = Range("D18:AA18")
'検索する文字列をkzに登録(仮登録。本来は別の処理で)
kz = 10
'検索で見つかったセルの値を変数に登録
'WorksheetFunctionで普段セルで使用している関数を使えるようになる。
'よって、IndexとMatchを使用できるようになった。
'Matchで希望の数字がいる列を探して、Indexでその列がいる場所の値を返している。
'ここの処理ではHitCellとkzは同じ数字になるけど、変数の型が違うから行った処理？
Set HitCell = WorksheetFunction.Index(targetRng, 1, WorksheetFunction.Match(kz, targetRng, 0))
'HitCell.Address;はRange.Address;
'Debug.Print HitCell.Address;でセル番地を出して、.Columnで列にしている
Debug.Print HitCell.Address; HitCell.Column
End Sub
解説ありがとうございます。
見覚えのないコードがばかりですので、いただいた解説を見ながらこれからコードを調べてきます。
先に（´・ω・`）様のコードを調べた結果、やはり不明点が残りましたので、高確率で追加で質問するかと思います。
お忙しい中恐縮ですが、その際はご教授いただければ幸いです。
お返事ありがとうございます。
部分一致と完全一致による検索結果の違いから、オプションを知らない方が検索した際に必要な結果が見つからずに悩む・変なところを触る・呼び出されることは、当方にとっては防げるなら防ぎたい手間です。
こちらはエクセルの知識ではなく、考え方やキャパシティの話になる為、事情説明として。
Findを使用→完全一致で検索→検索がデフォルトと変わってしまうという流れからFind以外と思いましたが、変数を利用すれば、Findを使用してもデフォルト設定のまま検索することができるのですね。
今回は他の方に教えていただいたコードで処理ができそうな気配がある為そちらで処理を行おうと思いますが、Findでデフォルト設定のままに処理を行いたい際にまた調べてみます。
検索方法を教えていただきありがとうございます。
（みみ） 2024/12/25(水) 14:24:25
コードの確認を行いました。
理想通り動いております。ありがとうございます。
解説いただいたことにより1点を除き理解することができました。
その1点の不明箇所なのですが、match関数を使用する際の「Application」は、「WorksheetFunction」と同じく普段セルで使用している関数が使えるようになるコードでしょうか？
「WorksheetFunction」を使用するとエラーが発生した際にマクロが止まる為、エラーで止まらない記述の「Application」を用いたのではないかと思いましたが、意味合いが違っていたらお手数ですがご説明お願いいたします。
Sub test()
'変数の宣言
Dim Rng As Range '検索範囲
Dim i As Variant 'Match関数の返り値
Dim j As Long '列番号
Dim Key As Long '検索値
'検索値を仮に「1」とする(本来は別の処理で登録)
Key = 1
'検索するセル範囲をRngに代入
Set Rng = Range("D18:AA18")
'match関数で検索(D18:AA18の中で何番目か出る)
'Application.？WorksheetFunction.と同じく普段セルで使用している関数が使えるようになる？
i = Application.Match(Key, Rng, 0)
'i = WorksheetFunction.Match(Key, Rng, 0)
'↑の場合、0～11以外の文字が入力されたらiにエラーは入らずにマクロが止まる
'IsNumeric：半角全角問わず数字のみだったらTrueを返す式
'検索する文字が0～11以外だったら、Matchのときに見つかってないので、
'iにはエラーが入っている=False
'Match関数の返り値が数値ならば、
'変数「Rng」のセル範囲のうちの「i番目」のセルの列番号を取得→j
If IsNumeric(i) Then
j = Rng.Cells(i).Column
End If
'メッセージボックスにjの値を表示
MsgBox j
End Sub
（みみ） 2024/12/25(水) 15:13:59
>D18:AA18の中に0～11の数字が入力されており、1は何列目にあるか、11は何列目にあるか？を知りたい。 この範囲に必ず0～11が存在することが条件で、
Sub test()
Dim x, myList
myList = Array(0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11)
x = Application.Match(myList, [a18:aa18], 0)
x = Evaluate("index(address(18,{" & Join(x, ",") & "},4),,)")
MsgBox Join(myList, Space(7)) & vbLf & Join(x, Space(3))
End Sub
（jindon） 2024/12/25(水) 15:15:44
>変数を利用すれば、Findを使用してもデフォルト設定のまま検索することができるのですね。
ええと、設定は引き継ぐのでそういう事ではないです つまり、デフォルトに戻すためにもう1度検索するだけです、空白セルでもいい 検索結果が変数にセットされるわけだから、それを使いたくなければ使わないだけ
（why） 2024/12/25(水) 16:17:40
>Find以外を使用した方法があれば、教えてください。
ということで、投稿したのですが
>見慣れない関数ばかりで、xに登録された列を抜き取ってその後どう処理するかなど調べてもまだまだ不明箇所があり、
そうですか、超初心者でもシート関数としてはまず習得すると思われる、Match 関数(vbaではメソッドと呼ぶこととしています) のみ使用しているのですが...
D18:AA18に 0-11の数値が入力されている場合 任意の数値の列をを夫々取得したいのなら
どこでもよいので、
=IFERROR(MATCH({0,1,2,3,4,5,6,7,8,9,10,11},D18:AA18,0)+3,"N/A")
と入力すればSpill するはずです。
Match関数の検索値に配列 {0,1,2,3,4,5,6,7,8,9,10,11} を指定すると、その各要素からの結果が一次元配列として返ります。
任意の数値を取得ということなら{1,11} のようにすると2列の結果が得られます。
これをメモリ上で(シートを使用しないで)再現しているだけです。
その位置を取得してから何をするか不明ですが
Sub test()
Dim x, myList, i&
myList = Array(1, 11) '<--- 配列を調整
With Application
x = .IfError(.Match(myList, [d18:aa18], 0), 0)
End With
ReDim Preserve myList(1 To UBound(myList) + 1)
For i = 1 To UBound(myList)
If x(i) Then
MsgBox myList(i) & " In " & Cells(18, x(i) + 3).Address(0, 0)
Else
MsgBox myList(i) & " Not found"
End If
Next
End Sub
（jindon） 2024/12/27(金) 13:26:15
>再びFindで検索してもその検索結果は影響しないということですね
うまく伝えられなかったかも Dim 1回目変数結果 as range Dim 2回目ダミー as range デフォルトに戻すためのダミー変数
set 1回目変数結果 = 検索範囲.Find(・・・・ set 2回目ダミー = 検索範囲.Find(・・・・ 元に戻すコード とやるか
1回目と同じ変数なら、end sub の直前にでも元に戻すコードを書いておくとか どちらでも好きな方のタイミングで
（why） 2024/12/27(金) 13:38:57
ああ、それとmatchを使うなら数値と文字の数字の区別を はっきりさせておかないとダメです、その辺注意 （why） 2024/12/27(金) 13:48:26
よしなしごとをメモします。
> Range("D18:AA18").Select '検索範囲選択
> Selection.Find(What:=kz, LookAt:=xlWhole).Activate 'ctrl+f
> rt = ActiveCell.Column
こういうSelectとかActiveCell多用方式はできるだけ早く卒業されるとよいと思います。
まあ、ステップを踏んでということでしょうけど。
Set found = Range("D18:AA18").Find(What:=kz, LookAt:=xlWhole) 'ctrl+f
rt = found.Column
のようなことになるでしょう。
> マナ様 > こちら最後のrtの一文を置き換えるということで合っていますでしょうか？ 3行は不要で、提示のあった一行だけで済むという指摘です。 解釈そんなに難しかったですか？ MATCH関数のヘルプを確認することと、ROWS(18)が18行目の行全体を指していることがわかればよいのでは？ A18:C18にマッチ対象があれば別ですけど。
・一次元配列で完全一致ならMATCHが最適です。 ・FINDはより広い範囲の検索や、複数個所のマッチがある場合の繰り返し等で使います。
FINDで強調されるのは、使用する際にオプションを省略するな、というのがあります。 （過去の履歴に影響されるのは、LookIn、LookAt、SearchOrder、および MatchByte の4種類です） 使用後にデフォルトに戻せ、というのは余り強調されることはないですが、確かにそうかもしれません。 ご指摘のあった方法で簡単に対応できます。
（xyz） 2024/12/27(金) 14:01:30
皆様から教えていただいたことも調べておりますが、理解が追い付かず折角のご提案を活かせずに申し訳ありません。
Application.Matchとあったので、普段セルで使用するMatch関数を使用したことはわかったのですが、その前のArray(配列？)がわからず、
・Array、配列とは？
・これが検索対象全てのリストだとしたら、なぜ検索値に全て入っているのか？全部出す為？
・知りたいのは特定の数字が入力されている列の為、全部出した後どう抽出するのか？
など混乱しておりました。
また、[]や{}は確かどこかのエクセルのverから追加された新規機能ですが、その選択方法とついていない状態での選択方法の違いも理解できず、しかしわざわざ[]があるからには何かの必要性があるはず……と、全て追い付いていない状態です。
その後の処理ですが、列位置を取得してからはCellsと組み合わせて使用しています。
追加で書いていただいたコードは、また改めて関数の意味を調べながら把握できるよう努力して参ります。
度々ありがとうございます。
Findを使う場合、End subの前に入れた方が確実に他の処理に影響がない為、混乱が少なく済みそうですね。
また、Matchに関する注意事項もありがとうございます。
まだまだ卒業できる気はしませんが、皆様が組んでくださったコードを見ると変数を活用されていますので、早くそちらに追いつけるよう精進して参ります。
また、マナ様のコードですが、返信を書いたときには1行=既存の1行の置き換えかと思ったのですが、WorksheetFunctionを知った今は理解することができますね。
現状A18:C18にマッチ対象はないので、今後もないままか見極めた上で使用していきます。
MATCHとFINDについての解説もありがとうございます。
・一次元配列で完全一致ならMATCHが最適
・FINDはより広い範囲の検索や、複数個所のマッチがある場合の繰り返し等で使用（使用する際にオプションを省略するな）
上記を元に適切な使用タイミングで使えるように意識します。
（みみ） 2024/12/27(金) 15:00:40
みみさん 少々混乱させてしまったようですね、すみませんでした。 私は必要以外はFindメソッドは使用しないようにしています。 設定をデフォルトに戻すのか、vbaで使用する前の設定に戻すのかいちいち面倒ですので。 後々興味が出てきて、わからない部分の説明が必要な時には遠慮なく質問してください。 （jindon） 2024/12/27(金) 15:21:28
>WorksheetFunctionを知った今は理解することができますね
どう理解したのかわかりませんが、WorksheetFunctionを使った場合 見つからなかったときは、エラー値を返せないので実行エラーになると思います なわかで今でもド古いエクセル5か、95の時の書き方(Application.Matchなど)を使う場合があります
（why） 2024/12/27(金) 15:54:33
why様
WorksheetFunctionによって、普段セルで使用している関数を使えるようになる。と理解しました。
WorksheetFunctionとApplicationの違いはわからない為、わかるまではApplicationを避けてWorksheetFunctionを使用するつもりです。(Application.WorksheetFunction.関数とも別のようなので)
今回いただいたお返事から、もしかして古い表記方法なだけでエラー時の処理は違えど意味合いとしては一緒なのかなと思いました。
注意事項や豆知識？のようなお返事、大変勉強になっております。ありがとうございます。
（みみ） 2024/12/27(金) 16:12:06
>(Application.WorksheetFunction.関数とも別のようなので)
最近のエクセルは知りませんが 昔試した結果、 WorksheetFunction. は、Application.WorksheetFunction. を略しただけで同じです （why） 2024/12/27(金) 16:27:20
>その1点の不明箇所なのですが、match関数を使用する際の「Application」は、 >「WorksheetFunction」と同じく普段セルで使用している関数が使えるようになるコードでしょうか？ >「WorksheetFunction」を使用するとエラーが発生した際にマクロが止まる為、エラーで止まらない記述の >「Application」を用いたのではないかと思いましたが、意味合いが違っていたらお手数ですが >ご説明お願いいたします。
>謎のApplication.Match関数
https://infoxnet.co.jp/ftblog/index.php/2022/04/15/story519e104471364a5d/
う～ん。。。。
Application.Matchは、VBAの初期のころの書き方で、
WorksheetFunctionオブジェクトというものが出来る前の書き方です。
他にはパッと思いつくところではSortメソッドもいつのころからかSortオブジェクトになりました。
ご推察のとおり、
WorksheetFunction.Matchの方は、返り値がエラーのときは、その行がエラーになります。
Application.Matchの方は、返り値がエラーのときは、そのままエラー値を返します。(Varianr型の変数に受け取れる。）
なので、エラー回避の方法が変わります。
Application.Matchの方が、エラー回避が大げさにならないので、
掲示板の回答でもよく使われてます。
WorksheetFunctionについて
https://excel-ubara.com/excelvba4/EXCEL207.html
（まっつわん） 2024/12/27(金) 16:36:16
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.