エクセル関数をVBAにしたいのですが・・・

=INDEX(C:C,MATCH("*WH*",C1:C67,0))
をVBAで表現したいのですが、
C1:C67を変数にしたくて
例えばrw=67
C1:C67⇒Crw-66:Crwみたいにしたいのですが
どうしたらいいか調べてもうまくいかないへっぽこです・・・

実際にVBAコードを書いていただけると大変ありがたいです・・！！

質問者からの補足コメント

• tom04様
  さっそくありがとうございます！
  説明足らずで申し訳ありません(^o^;)
  C1:C67の中にWHが含まれるセルが1〜2個あるので、
  例えばＤ１にINDEX（MUCH）
  Ｄ2にINDEX（MUCH+1）みたいにして
  セルの値を返そうと思っています。
  
  その範囲が67行づつの変数で、最大600回ループ処理する感じ（範囲内が空白になったら終了）です。
  補足も駄文ですみません。
  質問はあくまで表題の件ですがつい頼ってしまう…。

  No.1の回答に寄せられた補足コメントです。 補足日時：2019/09/29 22:25

No.1 ベストアンサー 回答者： tom04 回答日時： 2019/09/29 21:31

こんばんは！

数式を拝見すると
C列の中からC61:C67の範囲内の「WH」を含むセルの値を返す！
というような数式に思えるのですが、
INDEXの配列範囲とMATCH関数の範囲がなぜ重複しているのかイマイチ理解ができません。
（お示しの数式では「WH」という文字列を含むセルがC1～C67セル内にあると必ずそのセル値が返りますよね。）
まぁ～！その辺はさておいて・・・

質問文にある「エクセル関数をVBAに」という方法ではありませんが
一例です。

Sub Sample1()
　Dim c As Range, myRng As Range
　　Set myRng = Range("C1:C67")
　　　Set c = myRng.Find(what:="WH", LookIn:=xlValues, lookat:=xlPart)
　　　　If Not c Is Nothing Then
　　　　　MsgBox c
　　　　End If
End Sub

※　上記コードでもお示しの数式と同じ結果になると思います。

※　お望みの方法でなければ無視してください。m(_ _)m

この回答へのお礼

大変参考になりました！！！これをもとに試行錯誤して達成しました！

お礼日時：2019/10/06 13:14

No.4 回答者： tom04 回答日時： 2019/09/30 20:42

No.1です。

＞C1:C67の中にWHが含まれるセルが1〜2個あるので・・・

MATCH関数を使っているというコトは
複数存在しても最初の行がヒットしますので、67行内に複数あっても最初のデータだけをD列に表示すれば良い！
という解釈です。

Sub Sample2()
　Dim i As Long, cnt As Long
　Dim c As Range, myRng As Range
　　For i = 1 To Cells(Rows.Count, "C").End(xlUp).Row Step 67
　　　Set myRng = Cells(i, "C").Resize(67)
　　　　cnt = cnt + 1
　　　　Set c = myRng.Find(what:="WH", LookIn:=xlValues, lookat:=xlPart)
　　　　　If Not c Is Nothing Then
　　　　　　Cells(cnt, "D") = c
　　　　　End If
　　Next i
End Sub

こんな感じではどうでしょうか？m(_ _)m

No.3 回答者： yoreyore 回答日時： 2019/09/30 17:08

ただの参考です。

WHの入っている文字列を、C列から探して、Ｄ1から下に並べるだけの処理です。
WHでなくても良いように、A3に入っている文字列を、C列から探します。
67件を探し出す総件数とする場合、A4に67と入れます。　A4=5　ならば、5件しか探しません。
最大600回ループの意味が分かりませんが、C列のデータ数には制限なしです。

Sub 参考()
' 、A3のセルの文字列を含む文字列を、C列で上から検索して、
'　　見つかった文字列を、D1から下に並べる
' ついでに、見つかった文字列のあった行番号を、E列にメモる
'　　　ただし見つかった文字列が累計でA4セルの件数になった場合は、
'　　　その件数までで動作を終了する
tais = Range("A3")
owar = Range("A4"): If Not IsNumeric(owar) Then owar = 67
kens = 0
Set atta = Range("C:C").Find(what:=Range("A3").Value, _
LookAt:=xlPart, MatchCase:=True, MatchByte:=True)
If atta Is Nothing Then Exit Sub
sento = atta.Row
Do
kens = kens + 1: Cells(kens, 4) = atta: Cells(kens, 5) = atta.Row
Set atta = Range("C:C").FindNext(after:=atta)
Loop Until (sento = atta.Row) Or (kens = owar)
End Sub

No.2 回答者： Qchan1962 回答日時： 2019/09/30 14:57

ご質問の目的を想像して回答します。

的違いなら、すみません。
INDEXの対象範囲がC列のみである事、引数をMATCHで抽出する。
ループで複数ある値を抽出するなどを踏まえると
VBAの場合、アプリケーション関数をしないで、べたに行う方が良いのではないでしょうか。
アプリケーション関数は、処理が早く良いのですが、どうせループするなら、
ループ内で条件による抽出が良いと思います。

回答を判断するのはあなたですが、、

Sub test()
Dim R As Range
Dim Ans
　　For Each R In Sheets(1).Range("C:C")　　’シートインデックス1番のシートのC列対象
　　　　If R.Value Like "*WH*" Then
　　　　　Ans = R.Value　　　　　　　　　　　’値を抽出　配列に入れるなり、表に書き出すなり、、、してください。
　　　　　Debug.Print Ans　　　　　　　　　　’結果をイミディエイトウィンドウに表示　使用時コメントアウト
　　　　End If
　　　　If R.Value = "" Then Exit For　　　　　’空白になったら終了
　　Next R
End Sub

べたに総当たりなので、作画の抑止などの手法を講じてください。

結果をイミディエイトに出力していますので結果を確認されてください。
複数列も回すなら、
For Each R In Sheets(1).Range(Cells(1, 変数), Cells(最終行, 変数))
変数は列№　通常カウント変数、最終行は変数か、確実に空欄になる行№
みたいな感じで