@Haru57636433 (haru .)posted at 2021-02-04

マクロ　VBA　日付をキーワードに検索し、該当する列番号を取得したい

Q&A

解決したいこと

エクセルマクロで、G3セルに日付を手入力（2020/9/1)して、（表示形式は日付）そのセルに入力されている日付を元に、V9:SN9に2020/9/1~2021/12/31まで入力されているセルの列番号を取得したいと思っています。

s1 = Range("G3").Text '検索対象が入力されているセル
Set rng1 = Range("V9:SN9") '列の検索範囲
Dim c As Integer 'Column列番号
Dim i As Long
Dim ix As Long
Dim ary1 As Date　'ここをVriantにしたら、ユーザー定義は定義されていませんのエラー

For i = 1 To 10000
        ary1 = rng1
        ix = 22 '日付列の開始列
        For ix = LBound(ary1) To UBound(ary1)
            If ary(9, ix) Like s1 Then
                c = rng1.Cells(9, ix).Colmun
            End If
        Next
    Next

このように作成しており、日付を配列に入れて、その中から該当するセルを見つけ
cへ列番号を格納するというイメージです。

発生している問題・エラー

配列がありません

というエラーが出てしまいます。
また、上記記載の通り、Vriantでary1を定義しようとすると、ユーザー定義は。。。というエラーが返ってきてしまいます。

自分で試したこと

日付なので、セル自体を日付の表示形式にしたり、標準にしたりと試しましたが、空の値が格納されたり、定義されていませんのエラーが出てしまいます。

また、Find.を使用して探し出そうとしましたが、同様に、空の値が格納されてしまいエラーが出てしまいました。

この形でなくとも良いのですが、上記の実現したいことを実現させる方法はありますでしょうか。
ツールの参照設定も調べてみたのですが、該当しそうな解消法が見当たりませんでしたので、ご質問させていただきました。

宜しくお願い致します。

0 likes

1Answer

@jinoji posted at 2021-02-05

やりたいことが全てくみ取れているか自信がないですが、こんな感じでしょうか。

Sub test()
Dim s1 As String, rng1 As Range
s1 = Range("G3").Text 
Set rng1 = Range("V9:SN9")
Dim c As Integer 
Dim i As Long
Dim ix As Long
Dim ary1 As Variant    'スペルミス修正（Vriant -> Variant）

ary1 = rng1             '配列に格納

'For i = 1 To 10000     'このループは何のため？
        
        'ix = 22        '不要？
        For ix = LBound(ary1, 2) To UBound(ary1, 2) 'ary1は二次元配列でループに使うのは二次元めの下限、上限
            If CDate(ary1(1, ix)) = CDate(s1) Then 'ary1の先頭は(9,22)ではなく(1,1)　　比較の仕方も変更
                c = rng1.Cells(1, ix).Column        'スペルミス修正（Colmun -> Column）
                Exit For                            '一致したらループを抜ける？
            End If
        Next
'    Next

End Sub

もしくは、仕様から推察するとこういう感じにする手もありそうです。

Sub test2()
    Dim c As Integer, rng1 As Range, rng2 As Range
    Set rng1 = Range("G3")
    Set rng2 = Range("V9")
    c = rng2.Column + (rng1.Value - rng2.Value)
End Sub

Sub test3()
    Dim ix As Long, c As Integer
    ix = WorksheetFunction.Match(Range("G3"), Range("V9:SN9"), False)
    c = Range("V9:SN9").Cells(1, ix).Column
    MsgBox c
End Sub

1Like

Comments

@Haru57636433
Questioner
ご返答頂きありがとうございます🙇

上記1つ目のコードを試してみて、
MsgBoxでcを出力させたのですが、値が0になってしまいました。

また、私の説明が言葉足らずなのでイメージしずらいかと思うのですが、
セルG3に検索したい日付を入力して、それを元に、
V9から横一列に日付が入力されており、その日付の列番号を取得したいという仕様です。

続きとして、日付が始まるV列の手前の列のS列にprimarykeyを縦一列に並べており、
その縦一列の中から、上記同様、あるセルに入力された文字をキーに行番号を取得するということもやって、

合わせて特定の行と列を取得⇒特定のセルの場所を把握する

ということをしたいと考えております。

現在他にも試しておるのですが、取り急ぎ、ご返答頂いた内容の
状況をお伝えさせていただきました🙇
@jinoji
cの値が0ということは、うまく検索できていないようですね。
For文のあとに以下のデバッグ文を入れて、イミディエイトウィンドウで確認してみてください。
（イミディエイトウィンドウはCtrl+Gで表示できます）

Debug.Print s1, CDate(s1), ix, rng1.Cells(1, ix).Address, CDate(ary1(1, ix)), CDate(ary1(1, ix)) = CDate(s1)

また、2つ目、3つ目も試してみてください。
@Haru57636433
Questioner
ご連絡が遅くなり申し訳ございません。

無事表示できました、本当にありがとうございます🙇

test2、test3ともに表示できました。
test2を見たときに、こんなやり方があるんだ、、すごい！
と思いました😂

とても勉強になります、ありがとうございます🙇
@Haru57636433
Questioner
度々恐れ入ります。

先のご返答のおかげで、列番号は日付を変更しても問題なく取得できたのですが、
続けて行いたい行番号の取得がうまくいきません。

【仕様】
G4とG5に入力された値を元に、G6へ関数=G4&G5で文字列を出力しております。
仮に、G4は「35」G5は「行番号」と入力すれば、G6は「35行番号」という文字が出力されます。

そして、S列に縦一列に、「35行番号」のように文字列を列挙しており、該当する文字のあるセルの行番号を取得しようとしております。
ちなみに、S列は4つずつ同じものが縦に並んでいる仕様で、S列の1番上が「1行番号」という入力値だった場合、それ以下は、「1行番号」という文字が3列分＝合計4列並んでいる状態です。ここから、5行目には「2行番号」（以下3つは、＝を使用して「2行番号」と続いてます。）という文字が、9行目には「3行番号」といった形です。

【作成した関数】
上記ご回答参考に、

Dim iy As Long
Dim r As Integer

iy = WorksheetFunction.Match(Range("G6"), Range("S:S"), False)
r = Range("S:S").Cells(iy, 1).Column
という形で作成しましたが

10行目を取得するはずが19行目を取得
16行目を取得するはずが19行目を取得

という返り値になってしまいます。

関数で入力されている文字を元に、関数で入力されている値を検索しているせいかと思うのですが、うまく解決できておりません。

大変恐縮ですが、こちらの原因も、上記質問に関連付けた形でご質問させていただきたいです🙇😂
@jinoji
今回は縦に探すのでColumnの代わりにRowにします。
なお、この場合rとiy は結局同じ値になると思います。
@Haru57636433
Questioner
そうでした😂
お恥ずかしい。。。

そうか！行番号なので同じになるのですね！

r = WorksheetFunction.Match(Range("G6"), Range("S:S"), False)

この形で作成しなおしまして、無事欲しい値を取得できました！！

何度もありがとうございました🙇
@jinoji
うまくいったようで何よりです。

> この場合rとiy は結局同じ値になる

もう少し丁寧にいうと、Matchは第二引数の範囲の何個目で見つかるか、という値なので、そのままだと相対的な位置だから、「その範囲のiy番目のセル」の、シート全体の中での位置を改めて取得したんですけど、
列全体とか行全体を範囲に指定すると、1行目から（1列目から）数えて何個目、ということになるから、結局はそのまま行番号（列番号）と同じになる、ということです。
@Haru57636433
Questioner
なるほど、、ご丁寧に解説もありがとうございます！
とっても助かりました😊

Are you sure you want to delete the question?

マクロ　VBA　日付をキーワードに検索し、該当する列番号を取得したい

解決したいこと

発生している問題・エラー

自分で試したこと

1Answer

Comments

Your answer might help someone💌

マクロ VBA 日付をキーワードに検索し、該当する列番号を取得したい

解決したいこと

発生している問題・エラー

自分で試したこと

1Answer

Comments

Your answer might help someone💌

マクロ　VBA　日付をキーワードに検索し、該当する列番号を取得したい