EXCEL VBAコミュの教えてください。行方向の不定範囲指定について

突然の質問ですみません。
初心者なので、どなたか詳しい方がいらっしゃればお力を貸してください。

下記はテキストボックスに入力された文字列を条件にしてフィルタオプションを作動させるためのマクロです。

Mixiの右上にあるようなあいまい検索用のキーワード入力欄です。

テキストボックスにはスペース区切りで複数の検索条件のキーワード入力を可としていることからSprit関数で分割してセルに代入するよう仕組んでいます。
このセルをオートフィルタの条件としてAdvancedFilterを実行しています。

このケースでは「○○または△△を含む」を意図していますので、Z列に縦方向にキーワードを並べています。

今回、この式をベースに「○○を含む△△を含む」としたいのですが、具体的な式を書けません。

というのは、いくつ入力されるかわからないキーワードに対してアルファベットで表わされる行方向のセル範囲をどう指定していいか分からないからです。

数字で表すことのできる
「Cells(i + 2, 26) = "=*" & moji_h(i) & "*"　」の部分は
「Cells(1,i+27) = "=*" & moji_h(i) & "*"」に変えればよさそうですが、

アルファベットで表わす
「Columns("Z:Z").NumberFormatLocal = "@"」や
「ZRow = Range("Z1").End(xlDown).Row」、
「CriteriaRange:=Range("Z1:Z" & ZRow)」に相当する部分を表現できません。

どなたか知恵を貸してください。
よろしくお願いします。

以下、現行の（○○または△△を含む）AdvancedFilterのマクロです。

Private Sub CommandButton1_Click()

Dim moji_h As Variant
If ActiveSheet.TextBox1.Text = "" Then
MsgBox "キーワードを入力してください"

Exit Sub

End If
moji_h = Split(ActiveSheet.TextBox1.Text, " ")　　→スペースで分割
FRow = Range("F9").End(xlDown).Row　　　　　　　　→F列が検索対象
Columns("Z:Z").NumberFormatLocal = "@"　　　　　　
Cells(1, 26) = Cells(8, 6).Value　　　　　　　　　→F列の表題をZ1に入れる

For i = 0 To UBound(moji_h)　　　　　　　　　　　　→分割したキーワードの個数をループ
If moji_h(i) <> "" Then
Cells(i + 2, 26) = "=*" & moji_h(i) & "*"　　　　　→1つ目のキーワードをZ2へ
End If
Next i　　　　　　　　　　　　　　　　　　　　　　　→2つ目以降のキーワードをZ3以下へ　　

ZRow = Range("Z1").End(xlDown).Row　　　　　　　　　→検索条件になるキーワード
Range("A8:F" & FRow).AdvancedFilter _
Action:=xlFilterInPlace, _
CriteriaRange:=Range("Z1:Z" & ZRow), _
Unique:=False

MsgBox "検索終了"

Columns("Z:Z").ClearContents
Columns("Z:Z").NumberFormatLocal = "G/標準"

End Sub

コメント(5)

最初
全て
最新の40件

[1] mixiユーザー 12月10日 00:44

簡単なヒントおば、

Chr(n) ・・・・「n」に指定した正の整数に対応する文字列を返す。

例)
Chr(65) = A 、Chr(66) = B 、～、Chr(90) = Z

[2] mixiユーザー 12月10日 01:04

CHR　…　Z列以降だったらどうなるんでしょう…

---

.OFFSET　を使うとか

列選択なら
（Z列）
CELLS(1,26).ENTIRECOLUMN とか

RANGEの引数にCELLSを使うこともできます
>Range("Z1:Z" & ZRow)
なら
RANGE(CELLS(1,26),CELLS(ZROW,26))　とか色々できそうですが…

[5] mixiユーザー 12月15日 23:03

みなさんありがとうございます。
大変参考になりました。
今回、みなさんの助言のおかげで悪戦苦闘の末、なんとか思い通りのコードが作成できました。

Private Sub CommandButton3_Click()

Dim moji_h As Variant

If ActiveSheet.TextBox1.Text = "" Then
MsgBox "キーワードを入力してください"
Exit Sub
End If

If ActiveSheet.FilterMode = True Then
ActiveSheet.ShowAllData 'AdvancedFilter解除
End If

moji_h = Replace(ActiveSheet.TextBox1.Text, "　", " ")
moji_h = Split(moji_h, " ")
FRow = Range("B9").End(xlDown).Row
Range(Cells(1, 26), Cells(2, 100)).NumberFormatLocal = "@"

For i = 0 To UBound(moji_h)
If moji_h(i) <> "" Then
Cells(1, i + 26) = Cells(8, 6).Value
Cells(2, i + 26) = "=*" & moji_h(i) & "*"
End If
Next i

Range("F8:F" & FRow).AdvancedFilter _
Action:=xlFilterInPlace, _
CriteriaRange:=Range(Cells(1, 26), Cells(2, i + 26)), _
Unique:=False

MsgBox "検索終了"

Range(Cells(1, 26), Cells(2, 100)).ClearContents
Range(Cells(1, 26), Cells(2, 100)).NumberFormatLocal = "G/標準"

End Sub

ログインすると、みんなのコメントがもっと見れるよ