マクロ 関数の設置 「'」クオーテーションマークが勝手に入ってしまう

質問

http://oshiete1.goo.ne.jp/qa2917251.html
で問題解決していただいた者です。たびたび初心者的質問、失礼いたします。
以下のマクロを実行して、
=SUMIF(シート2!$B$1:$B$200,"*"&B4&"*",シート2!$C$1:$C$200)
という関数を設置しようとしています。
－－－－－
ActiveCell.SpecialCells(xlLastCell).Select
データ下 = Range("シート2!B1").End(xlDown).Row
数値下 = Range("シート2!C1").End(xlDown).Row
データ = 2
数値 = 3
選択範囲1 = "R1" & "C" & データ & ":R" & データ下 & "C" & データ
選択範囲2 = "R1" & "C" & 数値 & ":R" & 数値下 & "C" & 数値
数式 = "=SUMIF(シート2!" & 選択範囲1 & ",""*""&B4&""*"",シート2!" & 選択範囲2 & ")"
Range("A1").Formula = 数式
－－－－－

ところが、実際に上記を動かしますと、
=SUMIF(シート2!$B$1:$B$200,"*"&'B4'&"*",シート2!$C$1:$C$200)
真ん中あたりの検索条件、「B4」の両となりに「'(シングルクオーテーション)」が勝手に付加されてしまいます。

初歩的なことで大変恐縮ですが、これの原因と対策をご教示いただけないでしょうか？
どうかよろしくお願いいたします。

回答No.3

chie65536 · ベストアンサー

因みに、

選択範囲1 = "R1" & "C" & データ & ":R" & データ下 & "C" & データ
選択範囲2 = "R1" & "C" & 数値 & ":R" & 数値下 & "C" & 数値

はそのままで、

数式 = "=SUMIF(シート2!" & 選択範囲1 & ",""*""&B4&""*"",シート2!" & 選択範囲2 & ")"

だけを直すなら、

数式 = "=SUMIF(シート2!" & 選択範囲1 & ",""*""&R[3]C[1]&""*"",シート2!" & 選択範囲2 & ")"

にします。

"R[3]C[1]"は、結果を"B4"のように相対参照にする為で、数式を入れるA1セルからの相対位置で「A1から３行下の１カラム右」を指定しています。

No,1の回答のように絶対参照で"R4C2"とやってしまうと、結果の数式が絶対参照の"$B$4"になってしまいます。

行数、数値扱いのカラム位置から、セル参照文字列を作る場合、Address()を使うのが本筋です。

お礼

お礼が遅くなりまして、申し訳ありません。
ていねいな修正例までありがとうございました！
Ｎｏ．２のご回答の方では、私の知識範囲では理解できませんでしたが、
こちらのサンプルはわかりやすくすぐ反映することができました。
Address()　の件は使いこなせるよう調べておきます。
大変助かりました。

回答No.4

Wendy02

こんばんは。

現実的には、このような数式を作るマクロよりも、直接、マクロで作ったほうが早いだろうとは思います。いずれにしても、もう少し、プログラムらしく、すっきりと書いたほうがよいかと思います。

Sub TestSample()
Dim sh1 As Worksheet
Dim sh2 As Worksheet
Dim rng1 As Range
Dim rng2 As Range

Set sh1 = Worksheets("シート1")
Set sh2 = Worksheets("シート2")

　With sh2
　　Set rng1 = .Range("B1", .Range("B65536").End(xlUp))
　　Set rng2 = rng1.Offset(, 1)
　End With
　sh1.Range("A1").FormulaLocal = _
　"=SUMIF(" & sh2.Name & "!" & rng1.Address & ",""*"" & B4 &""*""," & sh2.Name & "!" & rng2.Address & ")"

Set rng1 = Nothing
Set rng2 = Nothing
Set sh1 = Nothing
Set sh2 = Nothing
End Sub

R1C1 方式なら、こんな感じかな？A1方式と混在なのは、いかにもおかしいけれども。「'」が入るのは、R1C1方式と混在しているからでしょうね。

sh1.Range("A1").FormulaLocal = _
　　"=SUMIF(" & sh2.Name & "!" & rng1.Address(1, 1, xlR1C1) & ",""*""&" & Range("B4").Address(0, 0, xlR1C1) & "&""*""," & sh2.Name & "!" & rng2.Address(1, 1, xlR1C1) & ")"

お礼

お礼遅くなってしまい申し訳ありません。
サンプルをいただきありがとうございました。
こちら実行してみましたところ、うまくいきました！
・・しかし、使いこなせる範囲外の例なので、応用がききません。。
（色々探していた際、似たようなサンプルを見たことがあったので、こちらのように書けばより適しているのだとは思います）
保存させていただき、内容調べて使えるようにしてみます！
ありがとうございました！

回答No.2

chie65536

選択範囲1 = "R1" & "C" & データ & ":R" & データ下 & "C" & データ
選択範囲2 = "R1" & "C" & 数値 & ":R" & 数値下 & "C" & 数値
数式 = "=SUMIF(シート2!" & 選択範囲1 & ",""*""&B4&""*"",シート2!" & 選択範囲2 & ")"

を

選択範囲1 = "シート2!" & Worksheets("シート2").Cells(1, データ).Address() & ":" & Worksheets("シート2").Cells(データ下, データ).Address()
選択範囲2 = "シート2!" & Worksheets("シート2").Cells(1, 数値).Address() & ":" & Worksheets("シート2").Cells(数値下, 数値).Address()
数式 = "=SUMIF(" & 選択範囲1 & ",""*"" & B4 & ""*""," & 選択範囲2 & ")"

に修正。

お礼

お返事遅くなりまして申し訳ございません。
こちら、ためしてみましたところうまくいったのですが、

このマクロを作成したあと、Ｂ列の下のセルにずらりとこれを
複写していくマクロをこのあとに実行する予定で、
絶対参照だと具合がわるいのです。。。
本当にありがとうございました！

補足

申し訳ありません、お礼の投稿場所をまちがえました。
Ｎｏ．１の方へのお礼文でした。

回答No.1

dsuekichi

> 選択範囲2 = "R1" & "C" & 数値 & ":R" & 数値下 & "C" & 数値
こちらは、「R1C1形式」でセルを指定しているのに、

> 数式 = "=SUMIF(シート2!" & 選択範囲1 & ",""*""&B4&""*"",シート2!" & 選択範囲2 & ")"

こちらは、「A1形式」でセルを指定と、統一されていないからでは？

> 数式 = "=SUMIF(シート2!" & 選択範囲1 & ",""*""&R4C2&""*"",シート2!" & 選択範囲2 & ")"
とか・・・

お礼

お返事遅くなりまして申し訳ございません。
こちら、ためしてみましたところうまくいったのですが、

このマクロを作成したあと、Ｂ列の下のセルにずらりとこれを
複写していくマクロをこのあとに実行する予定で、
絶対参照だと具合がわるいのです。。。
本当にありがとうございました！