Evaluateを使ってマクロに記述した関数に変数を使う方法

Evaluateを使ってマクロに記述した関数に変数を使う方法

"=SUMPRODUCT((ISERROR(FIND(A1,B1:B100))=FALSE)*(C1:C100<=10))"

という関数を普段使用しています。
ふとこの関数をマクロで実行させようと思い（理由はありません、思い付きです）、

Range("D1").Value = Evaluate("SUMPRODUCT((ISERROR(FIND(A1,B1:B100))=FALSE)*(C1:C100<=10))")

としてみました。うまくいきました。
次に、

Dim i As Long
For i = 1 To 5
Range("D" & i).Value = Evaluate("SUMPRODUCT((ISERROR(FIND(A1,B1:B100))=FALSE)*(C1:C100<=10))")
next

としてみました。ここで考えたのですが、

FIND(A1,B1:B100)

この部分の「Ａ1」を「i」を使って（つまりRange("D" & i)に連動させて）変数にすることは出来るのでしょうか？
ご教示願います。

今回は単なる好奇心で普段使っている関数をマクロで表現できないかなと思った次第で、どうしても知りたい！必要だ!というわけではありません。
お暇なときにお願いします。

No.2 ベストアンサー 回答者： Wendy02 回答日時： 2010/04/27 19:21

>今回は単なる好奇心で普段使っている関数をマクロで表現できないかなと思った次第で、どうしても知りたい

今回のようなマクロで、配列数式を埋め込むというのは、マクロとは少し違う問題です。それに、Evaluate　内は、SUMPRODUCT 関数は必要ないのです。配列数式に、インクリメンタル変数を埋め込むのは簡単ですが、Excelのバージョンなどに影響受けてしまいますから、あまり多用すべきではないと思います。
それを、一般プロシージャで表現できて、始めて、マクロが分かるということになると思います。

'-------------------------------------------

Sub Test1()
　　Dim i As Long
　　For i = 1 To 5
　　　　Cells(i, 4).Value = Evaluate("SUM((ISERROR(FIND(A" & i & ",B1:B100))=FALSE)*(C1:C100<=10))")
　　Next
End Sub

'以下は同じ内容のプロシージャです。
'-------------------------------------------
Sub Test2()
　　Dim i As Long, j As Long
　　Dim strTxt As String
　　Dim cnt As Long
　　For i = 1 To 5
　　　　strTxt = Cells(i, 1).Value
　　　　For j = 1 To 100
　　　　　　If Cells(j, 2).Value Like "*" & strTxt & "*" Then
　　　　　　　　If Cells(j, 3).Value <= 10 Then
　　　　　　　　　　cnt = cnt + 1
　　　　　　　　End If
　　　　　　End If
　　　　Next
　　　　Cells(i, 5).Value = cnt
　　　　cnt = 0
　　Next
End Sub

この回答へのお礼

ご回答ありがとうございます。
自分でも違う方法で動かすことができましたが、いろいろな方法があるようでとても面白い世界だなと思います。
ご教示いただいた内容を参考に勉強してみます。

＞それを、一般プロシージャで表現できて、始めて、マクロが分かるということになると思います。

ここにたどり着くまで長い道のりなんでしょうね。頑張ってみます。

お礼日時：2010/04/28 09:53

No.1 回答者： KenKen_SP 回答日時： 2010/04/27 13:57

この例で Evaluate に渡している引数は文字列なのだから、

　　Evaluate("SUMPRODUCT((ISERROR(FIND(A" & Cstr(i) & ",B1:B100...（略）")

のように & 演算子で単純に連結すればいいんじゃないかな。
ですが、私ならきっとこうします。

With Range("D1:D5")
　　.Formula = "=SUMPRODUCT((ISERROR(FIND(A1,$B$1:$B$100))=FALSE)*($C$1:$C$100<=10))"
　　.Value = .Value ' 数式を値に変換
End With

数式の内容は見てませんけど、このようにすれば A1 の部分はちょうど
数式のフィルのように変化します。変化させたくない部分は絶対参照で。
A1、A3、A5 のようなパターンならループでの処理になりますが。

この回答へのお礼

ご回答ありがとうございます。
VBAやマクロにちょっと興味があるので、暇を見つけてはチョコチョコと独学でいじっている程度なのですが
そろそろ本格的に勉強してみようかと思います。

お礼日時：2010/04/28 09:50