VBAでシート名を自動入力時のINDIRECT

質問

C1セルに日付を入れています。それがVBAで自動でシート名に入力されるようにしています。
例えば、「2014年 9月1日」と入れていると、シート名も「2014年 9月1日」となります。

そのようなシートを集計シートで値を合計していきたいと思っているのですが、INDIRECT関数を使って、シート名を参照しようとしてもエラーになってしまします。

例えば、集計シートのA1セルに、「2014年 9月1日」と入力しておいて、、「2014年 9月1日」シートのB1セルに「20」といれているとします。通常は、=INDIRECT(A1&"!B1")という関数を集計シートのA2セルにいれると、「20」と表示されますが、VBAでシート名を取得している場合はエラーになってしまいます。この場合、無理なのでしょうか？

また、もう1件、VBAのシート名取得で、「2014年 9月1日」は問題ないのですが、9月の9の前に半角スペースがあるのでそれを消すと、実行時エラー’1004’:が表示されて、C3セルの表示が###となります。C3セルの列幅を広げると、 、「　　　　　　　　　　　2014年9月1日」このような表示になっています。しかし、スペースが先頭に入力されているわけではありません。、「2014年9月1日」というシート名は手入力でいれても問題ないのですが、VBAで自動で入れた場合、エラーになるのは、プログラムに問題があるのでしょうか？下記プログラムを入力しています。

If Target.Address = "$C$1" Then Sh.name = Target.Range("A1").Value
End Sub

すいませんが、よろしくお願いいたします。

回答No.1

MackyNo1 · ベストアンサー

＞例えば、「2014年 9月1日」と入れていると、シート名も「2014年 9月1日」となります。

そもそも月の前に半角スペースを入れていますが、通常の日付と違って単なる文字列ですので、このような入力はご質問のような問題が発生する原因となります。

すなわち、ＶＢＡでシート名を取得しているからではなく、単純に日付シリアル値と文字列の日付が混在しているために正しいINDIRECT関数の参照式が表記できていないためと思われます。

また「Sh.name = Target.Range("A1").Value」の部分ですが、最後のValueはTextにしないと、通常の日付を入力した場合には、シート名にシリアル値の数字が表記されてしまいますので同じようなエラーの原因となります。

いずれにしろ、月の前に半角スペースを入れるような対応はあまりお勧めできませんので、そのあたりを見直したほうが良いと思います。。。

お礼

色々試していたら自己解決しました。
とりあえず、いけるみたいです。日付の場合、シート名は文字列として入力されているようです。だから、INDIRECTの参照先の日付もセルの書式設定で文字列にしました。文字列に変えるとシリアル値に変換されましたが、消去して再度入力するとINDIRECTを使った計算結果を表示してくれました。日付に文字列とかという考えがなく今回ヒントをいただいたおかげで解決できたと思います。
INDIRECTを使った他の文字でエラーになった理由は分かりません。もしかしたら、セルの書式設定で日付にしていたセルを使っていたことが影響していたのかもしれません。多分、普通の文字でもいけるはずです。
ありがとうございました。

補足

セルの書式設定→日付→2001年3月14日を選択して、そのままだと###だったのでC1～E1セルまで結合したら表示できました。ついでに、ValueをTextに変えると実行時エラー’1004’:がなくなりました。
しかし、INDIRECTの方は、表示しません。日付は全くだめなので、手入力でシート名を例えば「作業」と変えてみると、INDIRECTを使って表示しました。しかし、同じ「作業」というシート名でもVBAで自動でつけたものはINDIRECTでエラーになってしまいました。
根本的に使用不可の組み合わせなのでしょうかね?

回答No.4

MackyNo1

＞セルの書式設定→日付→2001年3月14日を選択して、そのままだと###だったのでC1～E1セルまで結合したら表示できました。

「###」が表示されるのは、単純に日付を入力したセルの列幅が足りないことを意味しており、問題がある挙動ではありません（文字列の日付の場合は、右のセルが空白なら、文字列がはみ出して表示されますので、このようになりません）。

＞しかし、INDIRECTの方は、表示しません。日付は全くだめで、手入力でシート名を例えば「作業」と変えてみると、INDIRECTを使って表示しました。しかし、同じ「作業」というシート名でもVBAで自動でつけたものはINDIRECTでエラーになってしまいました。

基本的にシート名を正しく参照しているならVBAに関係なく、INDIRECT関数が使用できるはずです。

たとえばシート名を手入力したセルと、シート名にカーソルを置いて右クリックし「名前の変更」を選択し、Ctrl+Cでコピーし、どこかのセルにCtrl+Vでそのシート名を貼り付けたセルを以下の式で比較しTRUEが返るかどうか調べてみてください（FALSEが返る場合は同じ値ではありません）、

=手入力したセル=シート名をコピー貼り付けしたセル

回答No.3

keithin

どこか別のセルに、今のINDIRECT関数の中身だけ計算して結果を確認してみると
=A1&"!B1"
意図していた結果が計算できていないことが確認できると思います。

「スペースを消した」（数式バーに2014/9/1と現れている状態に変えた）のが、計算できない直接の原因です。

で。
結局？
セルの値とシート名を両方とも「スペース無しの」2014年9月1日に「間違いなく統一した」のでしたら、

=INDIRECT(TEXT(C1,"yyyy年m月d日")&"!B1")
のようにします。

あるいは、前述と同じようにINDIRECT関数の中身だけ取り出して
=TEXT(C1,"yyyy年m月d日")&"!B1"
のように計算結果を確認し、あなたが選んだシート名の書式の通りに間違いなく計算できていることを確認して（修正して）計算してください。

お礼

すいません、補足に対する回答を拝見する前に、色々試していたら自己解決しました。
日付の書式設定をしていてもシート名は文字列として入力されているようです。だから、INDIRECTの参照先の日付をセルの書式設定で文字列にしました。文字列に変えるとシリアル値に変換されましたが、消去して再度入力するとINDIRECTを使った計算結果を表示してくれました。データの方の書式設定はそのまま日付で問題ありませんでした。日付に文字列とかという考えがなく今回ヒントをいただいたおかげで解決できたと思います。ありがとうございました。

回答No.2

keithin

ご相談の直接の原因としては、多分、文字列で入力されている状況（数式バーとセルとが同じ「2014年 9月1日」で表示されている）と、日付で入力されている状況（数式バーで見ると2014/12/4のように表示されている）とが、混在しちゃってるんじゃないかと思います。

どうしたら直せるのかは、マクロでやってるシート名の方をキチンとしてからじゃないと解決できません。

＃ところで
「□９月」のように１桁の時にスペースを補ってるのは、月のほうだけなの？日のほうはやってないの？
といった部分で「例えば」のつもりの情報提供が間違ってると、折角皆さんが回答を考えてくれても無駄になっちゃいますよ？

複数のご相談内容を１つのご質問に詰め込んでる事もあって、どこに何を手入力してて、どこはマクロでやらせてるのか不明なので、このご相談ではどこを手直したらいいかのアドバイスは割愛しますが、もう一度マクロからやり直した方が正解のように見えます。
ヤリタイ事を１つずつ箇条書きに整理して、ご相談を投稿し直してみてください。

補足

２つの質問を混ぜて投稿し、申し訳ございません。
マクロの日付の方は解決しました。
スペースが入っていたのは月の方だけです。直したので今はスペースは入っていません。マクロの実行時エラーもなくなりました。今の表示は数字バーの方は2014/9/1で、セルの方は2014年9月1日になっています。集計のシートの日付欄も同じ書式です。

（セルの書式設定→日付→2001年3月14日を選択して、そのままだと###だったのでC1～E1セルまで結合したら表示できました。ついでに、ValueをTextに変えると実行時エラー’1004’:がなくなりました。）

しかし、INDIRECTの方は、表示しません。日付は全くだめで、手入力でシート名を例えば「作業」と変えてみると、INDIRECTを使って表示しました。しかし、同じ「作業」というシート名でもVBAで自動でつけたものはINDIRECTでエラーになってしまいました。
根本的に使用不可の組み合わせなのでしょうかね?