Qiita Teams that are logged in
You are not logged in to any team

Log in to Qiita Team
Community
OrganizationAdvent CalendarQiitadon (β)
Service
Qiita JobsQiita ZineQiita Blog
@Haru57636433
Revisions
Report this question
Subscribe question
Help us understand the problem. What is going on with this question?
Q&A

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

解決したいこと

エクセルマクロで、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
1
Answer
Report this answer

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

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
1
ご返答頂きありがとうございます🙇

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


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

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

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

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

現在他にも試しておるのですが、取り急ぎ、ご返答頂いた内容の
状況をお伝えさせていただきました🙇
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つ目も試してみてください。
ご連絡が遅くなり申し訳ございません。

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

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

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

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

【仕様】
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行目を取得

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

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

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

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


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


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

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


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

もう少し丁寧にいうと、Matchは第二引数の範囲の何個目で見つかるか、という値なので、そのままだと相対的な位置だから、「その範囲のiy番目のセル」の、シート全体の中での位置を改めて取得したんですけど、
列全体とか行全体を範囲に指定すると、1行目から(1列目から)数えて何個目、ということになるから、結局はそのまま行番号(列番号)と同じになる、ということです。
なるほど、、ご丁寧に解説もありがとうございます!
とっても助かりました😊
View the remaining 7 comments.
Help us understand the problem. What is going on with this answer?
Sign up for free and join this conversation.
Sign Up
If you already have a Qiita account Login