EXCELでシート名を含む数式を作成するマクロ

質問

エクセルで困っています。
（WindowsXP+Excel2000です）

シートが5枚、シート名を「No1」～「No4」と「集計」と付けています。
シートの体裁は全て同じで、「No1」から「No4」のシートを串刺し演算して「集計」シートに合計を表示させています。
=SUM('No1:No4'!A1)
のような感じです。

質問なんですが、「集計」シートのA2にNo2と入力し、A3にNo3と入力した後にマクロを実行すれば、A1の数式が
=SUM('No2:No3'!A1)
のように変わるマクロは出来ないでしょうか。
INDIRECTとか使ってみてもうまく行きません。
よろしくお願いします。

回答No.2

merlionXX · ベストアンサー

Sub macro1()
Sheets("集計").Range("A1").Formula = "=SUM('" & Range("A2").Value & ":" & Range("A3").Value & "'!A1)"
End Sub

ではいかがでしょう？

お礼

出来ました（喜）
ありがとうございました。
ちょっとした工夫なんですね。まだまだ勉強不足でした。

回答No.7

Wendy02

#5 のWendy02です。

修正があります。最後から9行目（大きく空白行が開いているところ）から、最後までを以下を貼り付けで書き換えてください。

=ThreeDSum("No1:No3",A1:B1)

のような場合に、前回のままですと計算できません。また、現在のコードですと、大文字・小文字の違いを無視してくれません。

　For j = FirstNo To LastNo
　　　If StrConv(Worksheets(j).Name, vbUpperCase) Like StrConv(BaseName, vbUpperCase) & "#*" Then
　　　　 If TypeName(Worksheets(j).Range(rngAddress)) = "Range" Then
　　　　　　dblSum = dblSum + WorksheetFunction.Sum(Worksheets(j).Range(rngAddress))
　　　　 End If
　　　End If
　 Next j
　 ThreeDSum = dblSum
End Function

お礼

検証していただいてたんですね。
ホントに申し訳ないです。ありがとうございました。

回答No.6

tmom

No.1です。
応用性はありませんが、No.2さんの回答でうまくいくはずです。「SUM(」の後と最後の「！A1）」の前二ヶ所に「'」を2箇所追加することで数字のみのシート名に対応しています。

お礼

おかげさまで、うまく出来ました。お手間取らせて申し訳ないです。
自分でやってると、「'」の数がワケが分からなくなってきたりで、まだまだ勉強不足です。
どうもありがとうございました。

回答No.5

Wendy02

こんばんは。

確かに、INDIRECTの串刺し計算は出来ませんね。確か、配列数式で出来たように思ったのですが、結果が出ませんでした。ただ、今回の場合、マクロではなく、ユーザー定義関数ですね。マクロですと、もう少し簡単ですね。

入力例:
**

=ThreeDSum(B2,A1)

B1:
No1:No4 　[ここは文字列]
×　'No2:No3'　このように['] を入れてはいけません。
また、数字だけのシート名の場合は、"1:3" としてください。通常は、[""]は、必要ありません。

**
「0512-1」～「0512-4」の場合

=ThreeDSum("0512-1:0512-4",A1)
または、

=ThreeDSum(B1,A1)

B1:
0512-1:0512-4

シート間で、別のシート名があれば、それは計算されません。

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

'<標準モジュール>
Option Explicit
Function ThreeDSum(SheetName As Variant, rng As Range)
　 '串刺しSum: ThreeDSum("Sheet1:Sheet3",A1)
　 Dim num As Integer
　 Dim i As Integer
　 Dim j As Integer
　 Dim BaseName As String
　 Dim FirstNo As Integer
　 Dim LastNo As Integer
　 Dim buf As Integer
　 Dim sh1 As String
　 Dim sh2 As String
　 Dim rngAddress As String
　 Dim dblSum As Double
　 Application.Volatile

　 '数字のシート名
　 If InStr(SheetName, """") > 0 Then
　　 SheetName = Mid$(SheetName, 2, Len(SheetName) - 2)
　 End If

　 num = InStr(SheetName, ":")
　 If num > 0 Then
　　　sh1 = Mid$(SheetName, 1, num - 1)
　　　sh1 = WorksheetFunction.Substitute(sh1, vbNullChar, "")
　　　sh2 = Mid$(SheetName, num + 1)
　　Else
　　　Exit Function
　 End If
　 For i = Len(sh1) To 1 Step -1
　 If IsNumeric(Mid$(sh1, i, 1)) = False Then Exit For
　 Next
　 BaseName = Mid$(sh1, 1, i)
　 rngAddress = rng.Address
　 If TypeName(Worksheets(sh1)) <> "Worksheet" Then
　　　Exit Function
　 End If
　 If TypeName(Worksheets(sh2)) <> "Worksheet" Then
　　　Exit Function
　 End If
　 FirstNo = Worksheets(sh1).Index
　 LastNo = Worksheets(sh2).Index
　 'シート名の順が逆になった時
　 If FirstNo > LastNo Then
　　　buf = FirstNo
　　　FirstNo = LastNo
　　　LastNo = buf
　 End If

　 For j = FirstNo To LastNo
　　　If Worksheets(j).Name Like BaseName & "#*" Then
　　　　 If VarType(Worksheets(j).Range(rngAddress)) = vbDouble Then
　　　　　　dblSum = dblSum + Worksheets(j).Range(rngAddress)
　　　　 End If
　　　End If
　 Next j
　 ThreeDSum = dblSum
End Function

一応、いくつかのパターンを想定しましたが、まだ十分に検証されておりません。

お礼

ご丁寧な回答頂きありがとうございます。
お時間とらせて申し訳ありません。
ユーザー定義関数と言う物を、恥ずかしながら初めて知りました。これは、使いこなせば便利な物のようですね。

やってみましたら、確かにうまく動きます。
あとは私自身のユーザー定義関数の理解度にかかっているような気がします。何がどうなって計算できているのか、今のところナニがなんだかって感じですので＾＾：

どうもありがとうございました。

回答No.4

bonaron

#3の回答を参考にして

Worksheets("集計").Range("B1").Formula = "=INDIRECT(""'"" & A2 & ""'!A1"")+INDIRECT(""'"" & A3 & ""'!A1"")"

のようにしてみました。

複数のセルに対応するのは工夫が必要ですね。

お礼

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

ご回答の方法で出来ましたが、実はどうしてもSUM関数を入れなければならない事情がありまして。
INDIRECT関数をマクロの中に突っ込もうとして悪戦苦闘していた次第で。。。

工夫するのもまた楽しいんですけど、今回は切羽詰っていました物で。。。
また勉強しなおしますm(__)m

回答No.3

odessa7

INDIRECTで出来ませんか？

　ご質問の場合ですと
=INDIRECT(A2&"!A1")+INDIRECT(A3&"!A1")
　などとすれば、表示されると思います。

　ご参考まで。

お礼

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

ご回答の方法で出来ましたが、実はどうしてもSUM関数を入れなければならない事情がありまして。
INDIRECT関数をマクロの中に突っ込もうとして悪戦苦闘していた次第で。。。

また勉強しなおしますm(__)m

回答No.1

tmom

こんな感じでいかがでしょうか？

Sub macro1()
Sheets("集計").Range("A1").Formula = "=SUM(" & Range("A2").Value & ":" & Range("A3").Value & "!A1)"
End Sub

お礼

早速解答頂きありがとうございました。

ご回答の方法でやると確かにうまく行きました。
ですが、非常に申し訳ないんですが、補足させてくださいm(__)m

補足

実は質問の内容を簡単にするために、シート名を簡略していました。
実際には「0512-1」～「0512-4」と「集計」と言うシート名です。
ご回答のマクロを書き換えて実行してみましたら数式が

=SUM(512-3:512-'4'!A1)
（A2に0512-3、A3に0512-4を入力。文字列形式で。）

のようになってしまい、何故か循環警告まで出てしまいました。
何とかならないでしょうか。
せっかく回答いただきながら申し訳ないです。。。