Excel VBAで・・・

質問

こんにちわ。VBA初心者ですが、よろしくお願いします。

下記のような表からの値の取得（値の走査）の処理を考えているのですが、どうも1つ1つのセルを比較して取得するやり方しか考えつかなくて、マクロの実行時間が長くなってしまいます。。。

A列　　B列　　 C列　　　D列
関東　千葉県　千葉市　中央区
関東　東京都　　　　　　　渋谷区
関東　　　　　　　横浜市
東北　福島県　福島市
関西　　　　　　　　　　　　　伏見区
北陸　　　　　　　新潟市

（A列にはエリア、B列には県、C列には市、D列には区が設定されてます。）

入力値がB,C,D列に全て一致した行のA列を返却させたいと思っています。
例えば、神奈川県横浜市港北区と入力した場合、関東を返して欲しいです。
ただし、空白のセルの箇所は見ない（何でも構わない）ため、
東京都新潟市中央区と入力した場合は北陸を返して欲しいのです。

私が思いつくのは、県を比較して、一致or空白なら次の列（市の比較）へ行き、不一致なら次の行で同じ処理を行うやり方しか思いつきません・・・

1つ1つの比較以外に何かやり方はございますでしょうか？
ExcelのFind関数などの利用も考えたのですが、空白の場合にうまく動かないので。。。
宜しければアドバイスをお願いします。

回答No.1

ka_na_de · ベストアンサー

こんばんは。

１行目に見出し行をあらかじめ入れておいて、
フィルターで検索値または「空白」で絞り込んで
いってはどうでしょうか？

実際には、県、市、区の検索値は変数にします。

例えば、
With Range("A1").CurrentRegion
　　.AutoFilter Field:=2, Criteria1:="=神奈川県", Operator:=xlOr, Criteria2:="="
　　.AutoFilter Field:=3, Criteria1:="=横浜市", Operator:=xlOr, Criteria2:="="
　　.AutoFilter Field:=4, Criteria1:="=中央区", Operator:=xlOr, Criteria2:="="
End With

お礼

ご回答ありがとうございます。
オートフィルタですか！
私も考えていたのですが、フィルタ後の値の取得が今ひとつ理解できなかったので忘れてました。。。
参考にさせて頂きます。

回答No.7

turuzou

No.6　追記です。
説明不足でご免なさい。
数式のコピーの仕方です。
E2を選択して、セルの右下隅に、マウスをあわせカーソルが＋に変わったら、ドラッグで下へ必要分コピーすることです。

お礼

ご回答ありがとうございます。
＃お礼が遅くなり申し訳ありません。
あの操作はオートフィルって名前だったんですね。
お恥ずかしい話ですが知りませんでした。。。
丁寧に説明して頂きありがとうございます。

回答No.6

turuzou

A1に入力し、関数で補助列(E列)に表示するるとして
E2へ　=IF(COUNTIF(A$1,"*"&B2&C2&D2&"*"),A2,"")　を、オートフィルで下へ

VBAならばCOUNTIFの代わりに InStr 関数を使用してみては。
・・・
If InStr("神奈川県横浜市港北区",cells(x,2)&cells(x,3)&cells(x,4)) > 0 Then
　答え = cells(x,1)
End If
・・・

お礼

ご回答ありがとうございます。
InStrを使用する方法は思いつきませんでした！
空白が入っていても大丈夫そうです。
ただ上の
>E2へ　=IF(COUNTIF(A$1,"*"&B2&C2&D2&"*"),A2,"")　を、オートフィルで下へ
のオートフィルで下への意味が私には理解が出来ませんでした(TдT)

回答No.5

mt2008

No.4です。
1行づつループを回して都道府県、市、区が空白の場合は「*」を代入した比較用の文字列（例：*新潟市*）を作り、検索文字列とLike演算子で比較すれば条件に一致しているかどうかは判ります。あとは複数行が検索条件に一致した場合どうするかです。

以下の例では最初に見つかった行を有効としています。

Sub Sample()
　Dim sString As String
　Dim sSearchString As String
　Dim sAnser As String

　Dim nRow　As Long

　sAnser = "該当無し"
　With ActiveSheet
　　sString = "東京都新潟市中央区"
　　nRow = 1
　　'A列が空白になるまでループ
　　Do While (.Cells(nRow, 1) <> "")
　　　'都道府県
　　　sSearchString = .Cells(nRow, 2)
　　　If .Cells(nRow, 2) = "" Then sSearchString = sSearchString & "*"

　　　'市
　　　sSearchString = sSearchString & .Cells(nRow, 3)
　　　If .Cells(nRow, 3) = "" Then sSearchString = sSearchString & "*"

　　　'区
　　　sSearchString = sSearchString & .Cells(nRow, 4)
　　　If .Cells(nRow, 4) = "" Then sSearchString = sSearchString & "*"

　　　'Likeで比較
　　　If sString Like sSearchString Then
　　　　sAnser = .Cells(nRow, 1)
　　　　Exit Do
　　　End If
　　　nRow = nRow + 1
　　Loop
　　MsgBox sAnser
End With
End Sub

お礼

ご回答ありがとうございます。
（＃４の回答と併せて書かせて頂きます）
ソースまで書いて頂きありがとうございます。
Like演算子。。。
お恥ずかしい話ですが知りませんでした。。。
ワイルドカードでの一致が出来ればと思っておりましたが、Likeを使えばいいんですね！
勉強になりました。
やはり行分のループを回す以外には難しいでしょうか。。。。

回答No.4

mt2008

> ただし、空白のセルの箇所は見ない（何でも構わない）ため、
> 東京都新潟市中央区と入力した場合は北陸を返して欲しいのです。

これが難しいですね。条件が良く判りません。
たとえば、「東京都新潟市伏見区」と入力された場合はどうしたいのですか？

私でしたら、紛らわしい入力は弾いてしまって完全一致だけを有効にしますが……。

補足

提示した情報に不足があり、ご不快な思いをさせて申し訳ありません。
複数の条件が当てはまった場合は、当てはまるデータのうち、一番上に設定してあるデータを取得したいと考えておりました。

回答No.3

Trick--o--

完全一致なら、3つのセルの値を連結してしまえばいいのでは

　Set 範囲 = Range(Cells(1, 1), Cells(Cells(65536, 1).End(xlUp).Row, 1))
　f = 検索値　 ' 検索する値をどこからか取得する　
　For Each r In 範囲
　　i = r.Row
　　t = Cells(i, 2) & Cells(i, 3) & Cells(i, 4)
　　If t = f Then
　　　答え = r　' A列の値をどこかに出力する
　　　Exit For
　　End If
　Next

行数だけループすることになるけど。

お礼

ご回答ありがとうございます。
同じ方法を考えたのですが、空白の場合を考えると今一つうまくないので。。。
やっぱり空白の仕様がネックなのでしょうか。。。

回答No.2

ka_na_de

＃１です。

ごめんなさい。
例の検索値に「=」は不要です。
訂正しておきます。

With Range("A1").CurrentRegion
　　.AutoFilter Field:=2, Criteria1:="神奈川県", Operator:=xlOr, Criteria2:=""
　　.AutoFilter Field:=3, Criteria1:="横浜市", Operator:=xlOr, Criteria2:=""
　　.AutoFilter Field:=4, Criteria1:="中央区", Operator:=xlOr, Criteria2:=""
End With

お礼

訂正ありがとうございます。
AutoFilterのHELPを見ると"="で空白のセルを抽出と書いてありますけど何ででしょうね？