マクロだと数式が表示される

https://oshiete.goo.ne.jp/qa/13135672.html
で質問したのですが、
　　　マクロでコピーすると数式が表示される

　　　sheet1 のA1 に　8/7
　　　sheet1 のA2 に　8/10
　　　sheet1 のA3 に　テスト
　　　と文字列として値が入っています。
　　　sheet2で、マクロの記録にして、Sheet2 A1 にて、= 後、sheet1を選択
　　　して Sheet1のA1を選択してEnter
　　　次いで、Sheet2にてA1をA3までオートフィル
　　　ここでマクロを終了して
　　　次に、マクロの開発で記録されたマクロを実行すると、
　　　=Sheet1!A1
　　　=Sheet1!A2
　　　=Sheet1!A3
　　　と数式になってしまう。

　　　今までふつうに数式の結果がコピーされてたと思うのですが、
　　　しかも、不思議なことに他の部分ではちゃんと計算結果が表示される
で
　>　代入先のセルの書式が「文字列」になっていたりしまませんか？
　>　式を入力する前に
　>　対象セル.NumberFormatLocal = "G/標準"
　>　などのように、書式設定をしてから記入すれば数式として解釈されると思います。
と回答をいただいたのですが、かえって悪化して
結果
表示は　　　　セルの内容は
#NAME?　　　=@'Sheet1!A2'
#NAME?　　　=@'Sheet1!A3'

もう少し具体的に説明します。
Sheet1(見積書と想定する。)Sheet2(納品書)
Sheet1　　A列は文字　10行とする
┏━━┳━━━━━┳━━┳━━━┳━━━━┓
┃日付┃品名　　　┃数量┃単価　┃　合計　┃
┣━━╋━━━━━╋━━╋━━━╋━━━━┫
┃9/6 ┃ペン　　　┃ 1┃ 150┃=C2*D2┃　E列は式
┣━━╋━━━━━╋━━╋━━━╋━━━━┫
┃9/10┃ノート　　┃ 2┃ 500┃　 1000┃　E列は式
┣━━╋━━━━━╋━━╋━━━╋━━━━┫
Sheet2　　★数量や単価が変わるので初期値は =Sheet1!RCだが・・
┏━━┳━━━━━┳━━┳━━━┳━━━━┓
┃日付┃品名　　　┃数量┃単価　┃　合計　┃
┣━━╋━━━━━╋━━╋━━━╋━━━━┫
┃ 9/5┃ペン　　　┃ 1┃ 160┃=C2*D2┃　E列は式
┣━━╋━━━━━╋━━╋━━━╋━━━━┫
┃9/11┃ノート　　┃ 3┃ 500┃　 1000┃　E列は式
┣━━╋━━━━━╋━━╋━━━╋━━━━┫
リセットボタンを作りたい。すべてを初期値に戻す。
マクロ登録
★Sheet1 select A2:E10
クリア
E2 [=C2*D2]
オートフィル　D2～D11
★Sheet2 select A2:E11
クリア
A2 [=Sheet1!A2]
オートフィル　A2～D11
E2 [=C2*D2]
オートフィル　D2～D11
・・・・・・
マクロを実行すると
日付 品名 数量 単価 合計
=Sheet1!RC =Sheet2!RC =Sheet3!RC =Sheet4!RC =Sheet5!RC
=Sheet1!RC =Sheet2!RC =Sheet3!RC =Sheet4!RC =Sheet5!RC
=Sheet1!RC =Sheet2!RC =Sheet3!RC =Sheet4!RC =Sheet5!RC

質問者からの補足コメント

• (経緯)インボイス対応の見積・納品・請求・領収・適格修正請求書を作成している。
  　　　テンプレートはできている。
  (目的)作業途中でテンプレートを上書きしたり、連続して作業したりのために、入力値をすべて消してテンプレートの状態に戻したい
  A列は"G/文字列"
  注)基本的には見積書に入力すれば、以後の表にはコピーされる。(=見積書!RC)
  注) 各表中で値が変わることがある。見積書→納品書で数量や単価、商品詳細とか・・
  　　見積書のA2:D4をクリア、ただしE列は、すべてのシートで=C*D
  　　以後、納品書では A2=Sheet1!A2 となる。オートフィルで下まで
  　　手作業では問題ない。マクロを記録してマクロ側からだと、数式が

  No.1の回答に寄せられた補足コメントです。 補足日時：2022/09/10 17:50

• Sub Macro1()
  Sheets("Sheet1").Select
  Range("A2:E4").Select
  Selection.ClearContents
  Sheets("Sheet2").Select
  ActiveSheet.Range("A2:E4").Select
  Selection.ClearContents
  ActiveSheet.Range("A2").Select
  ActiveCell.FormulaR1C1 = "=Sheet1!RC"
  ActiveSheet.Range("A2").Select
  Selection.AutoFill Destination:=Range("A2:A4"), Type:=xlFillDefault
  End Sub

    補足日時：2022/09/10 17:51

No.2 ベストアンサー 回答者： Qchan1962 回答日時： 2022/09/10 23:38

こんばんは

＞インボイス対応の見積・納品・請求・領収・適格修正請求書を作成している。
ハードル高いですね
私も昔作成したことがありますが、ながらで作ったとは言え、1か月以上かかった記憶がありますよ・・ネックは排他処理、セキュリティだったかと
もう10年以上前に変えました・・

＞(目的)作業途中でテンプレートを上書きしたり、連続して作業したりのために、入力値をすべて消してテンプレートの状態に戻したい

示されているテンプレートは入力シートの事でしょうか？
だとすると、数式はあらかじめシートに作成するのが簡単かも
（数式を入力するから違うかな・・）

マクロで行う部分は、
入力項目のクリアー
入力データのチェック、プリント（PDF）、保存など、
保存データの抽出など
でしょうか・・


それだけでも課題は沢山ありますし、基幹業務で大切な業務なので
プロに依頼されるか、販売されている汎用ツールを使う方が良い様な気がします


Sub Macro1()の記録マクロについて
＞A列は文字列
の状態でSub Macro1()を実行すると　=Sheet1!A1　と表示されるのは
理解されたと言う事でよろしいでしょうか

マクロで書式設定を変更するコードを加えると

Sub Macro1()
Dim nf As String
Sheets("Sheet1").Select
Range("A2:E4").Select
Selection.ClearContents
Sheets("Sheet2").Select
ActiveSheet.Range("A2:E4").Select
Selection.ClearContents
nf = Range("A2").NumberFormatLocal
Range("A2:A4").NumberFormatLocal = "G/標準"
ActiveSheet.Range("A2").Select
ActiveCell.FormulaR1C1 = "=Sheet1!RC"
ActiveSheet.Range("A2").Select
Selection.AutoFill Destination:=Range("A2:A4"), Type:=xlFillDefault
Range("A2:A4").NumberFormatLocal = nf
End Sub
になります

変数や書式設定の取得は記録できないので少しハードルが高くなりますが
端的に処理内容を検索すれば、参考サイトは沢山あります
今回の回答例のコードは <VBA 書式設定と取得>で見つけて添削しました

繰り返しになるかも知れませんが
コードではSheets("Sheet1")の対象シートの値がクリアーされていますね
そこを参照する数式を入力する事に　何か意味はあるのでしょうか？

一度、Sheets("Sheet2")に数式を入力しておけば、Sheets("Sheet2")の操作は不要では、と思いました。

テスト段階だとは思いますが、
=Sheet1!RC =Sheet2!RC =Sheet3!RC =Sheet4!RC =Sheet5!RC
は　一度、Sheets("Sheet2")を手作業で作成して　それを　各シートのコピペすれば良いと思いますしマクロで毎回行う作業では無いと思います
値のみを出力したいのなら別の話ですが・・

ちなみに上に挙げた　Sub Macro1()を添削すると（書き方は一例です）

Sub Macro1()
Dim nf As String
Sheets("Sheet1").Range("A2:E4").ClearContents
With Sheets("Sheet2").Range("A2:A4")
.ClearContents
nf = .Item(1).NumberFormatLocal
.NumberFormatLocal = "G/標準"
.FormulaR1C1 = "=Sheet1!RC"
.NumberFormatLocal = nf
End With
End Sub

この回答へのお礼

なんとかできました。ありがとうございます。

お礼日時：2022/09/17 17:13

No.1 回答者： fujillin 回答日時： 2022/09/10 15:02

前回回答者です。

追加の回答をしようとしたら締め切られていましたので、こちらに。

表示が
＞#NAME?
となるのは、エクセルが関数式として解釈しようとしているので、意図通りです。
ただし、設定された内容が関数式として解釈できないためにエラー表示になっているものと推測します。
どのようなコードで何を設定しているのかが不明ですので、こちらではこれ以上はわかりかねますが、通常の手操作でも
＞=@'Sheet1!A2'
のような入力は拒否されます。
（試してみればわかりますが、エラー表示にもなりません。）


※ 実際になさりたいことと、現状のコードの両方をきちんと示せば解決するのではないか思います。
どうしたいのかと、セルの位置関係がわかるようにしておくことが必要です。
（私が回答するとは限りませんけれど・・）

この回答へのお礼

他にもいろいろあって時間がかかりましたが、とりあえず満足できるものができました。

お礼日時：2022/09/17 17:12