エクセルVBA 不規則な条件に合致する場合

質問

エクセル2002　VBA　での質問です。
A列に100～9999の数値データが不規則に存在しており、

105　108　113　114　121　531　553　2160　・・・・・

のような不規則な値と合致するときに　B列の処理をしたい場合のコードはどのように記述しますか？

私は
for MyRow=1 to （最終行）
if (cells(MyRow,1) =105 or cells(MyRow,1) =108 or cells(MyRow,1) =113 ・・・）　then
B列の処理
end if
next MyRow

のように　If文　のなかで　or を用いて全てを記述するかElseif でつなぐ方法しか思いつきません。

抽出対象の値は今後メンテナンスする必要はないのでコード内に記述しても構わないのですが　or 　や　ElseIf　を多用するのもどうかと思っています。

※現実には50個ほどの条件での合致を検出したいです。

みなさんならどのように記述されるでしょうか？

回答No.4

keithin · ベストアンサー

作成例：
sub Excelに出来ることはExcelにやらせる()
　dim a as variant
　dim i as long
　a = array(105, 108, 113, 114, 121, 531, 553, 2160)

　range("1:1").insert
　range("A1").resize(1, ubound(a)+1) = a
　with range("B2:B" & range("A65536").end(xlup).row)
　　.formula = "=IF(COUNTIF($1:$1,A2),""○"","""")"
　　.value = .value
　end with
　range("1:1").delete shift:=xlshiftup
end sub

作成例：
sub マクロの中でゴリゴリやらせる()
　dim a as variant
　dim i as long
　dim j as long
　a = array(105, 108, 113, 114, 121, 531, 553, 2160)
　for i = 1 to range("A65536").end(xlup).row
　　for j = 0 to ubound(a)
　　　if cells(i, 1) = a(j) then
　　　　cells(i, 2) = "○"
　　　　exit for
　　　end if
　　next j
　next i
end sub

お礼

ご回答ありがとうございます。
二つも例示していただき感激です。

特に　Excelに出来ることはExcelにやらせる()での
ワークエリアとして行挿入＆削除　や
.value = .value　のような一見しただけでは何をしているのかわからないコードの意味を読めたときは感動してしまいました。

ゴリゴリやるのはループの入子で、まさにゴリゴリを感じてしまいます（＾＾）

回答No.5

imogasi

>B列の処理をしたい場合
の処理とは何？プログラムのコードの中ではないのだから、処理とは、同行のB列値を取るのではないのか？
あいまい。
値を取るならエクセルの常識関数VLOOKUPが使えるのでは。VBAでも使えるよ。
Sub test02()
Dim x As Integer
x = InputBox("コード")
MsgBox x
With ActiveSheet
y = Application.WorksheetFunction.VLookup(x, .Range("A1:B3"), 2, False)
MsgBox y
End With
End Sub
ーーーーーーーーー
モジュールのルーチン名（文字列）を得て、そのルーチンを実行するのは出来るかな？無理？

お礼

ご回答ありがとうございます。

>B列の処理をしたい場合
の処理とはデータクリア（セルを空白セルにする）したいのですが、今後は演算処理が要求されそうでもあります。

今後もご教示をお願いします

回答No.3

tom04

こんばんは！
色々やり方はあると思いますが、
IF・・・　OR　IF・・・　と続けても大変ですので、
一例です。
Sheet2のA列に合致（検索）データを入力しておきます。
その上で↓のコードのような感じではどうでしょうか？

Sub test()
Dim i As Long
Dim ws As Worksheet
Set ws = Worksheets(2)
Application.ScreenUpdating = False
For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row
If WorksheetFunction.CountIf(ws.Columns(1), Cells(i, 1)) Then
Cells(i, 2) =「B列の処理」
End If
Next i
Application.ScreenUpdating = True
End Sub

参考になりますかね？m(_ _)m

お礼

ご回答ありがとうございます。
ワークシート関数の　Countif　で一致確認をするのは、案外すっきりしたコマンドですね。

WorksheetFunction.　って冗長な気がしていたのですが実例を教えていただくと活用する気になってきます。

回答No.2

11zep

私なら合致の結果を返すFunctionを作成します。
そして照合する値を配列に格納してFor Next文で配列の数分
ループさせる方法をとります。可読性が良くなるのが好きなので。
以下のサンプルは、Excel2007で作成しています。

呼び出し元：
If ValExist(Cells(myRow,1) Then
　　一致した処理
Else
　　一致する値がない場合の処理
End If

Private Function ValExist(Atai As String) As Boolean
　　Dim MyValue() As String
　　Dim Cnt As Integer

　　MyValue = Split("105,110,120,125", ",")
　　For Cnt = 0 To UBound(MyValue)
　　　　If Atai = MyValue(Cnt) Then
　　　　　ValExist = True
　　　　　Exit For
　　　　End If
　　Next Cnt
End Function

お礼

ご回答ありがとうございます。
UBoundのコマンドは知らなかったので勉強になります。

Splitで配列にして、その要素数をUBoundで取得するのですね。
その配列に一致すればFunctionがTrueを返す。

応用できるようにがんばります(^^)

回答No.1

mar00

Select Caseを使う手もあると思います。
Select Case Range("A" & MyRow)
Case 113 To 118, 150 To 200
B列の処理
End Select
とか
Select Case Range("A" & MyRow)
Case Is > 150
B列の処理
End Select
などのように。

お礼

ご回答ありがとうございます。
or を　,　にするだけでもずいぶんすっきりしますね