エクセルでVBAでセルの値を転記するとエラーになる

質問

お世話になります。

だいぶ前にここで下記のコードを教わってかなりの期間、複数のＢｏｏｋで問題なく使用させてもらっていたのですが、最近不具合が発生していることに気付きました。（担当者が結果を手入力していたので正確な発生時期不明）

不具合の内容は別シート「計算表」で計算した結果（セルの値）をVBAで指定のシート「入力表」の指定の列「D列」の最下行に転記するとこの列の上部の平均値や最大値がエラ表示に変わってしまいます。

手入力やコピペで値を入れると正しく計算されます。
今まで複数のＢｏｏｋで使用しているVBAでこれまで問題なく動いていたのですが今日確認したら他のBookでも同じ不具合が発生していました。

手入力、コピペとVBAでの転記で何が違うのでしょうか？
非常に困っていますので解決手段、改善の可能性のある方法でもよいのでアドバイスをお願いします。

Sub 計算表()
Dim MyRange As Range
With ThisWorkbook
Set MyRange = _
.Sheets("入力表").Cells(Rows.Count, 4).End(xlUp)
MyRange.Offset(1, 0).Value = .Sheets("計算表").Range("C11").Value
Application.Goto Reference:=Worksheets("入力表").Columns("A"), Scroll:=True
End With
End Sub

回答No.8

HohoPapa · ベストアンサー

>思い起こせば、だいぶ前からファイルを開いた時には
>全ての列の計算結果がエラーになっている現象が有りました。
>しかし、列にデータが入力されると計算結果が表示されるので、
>HohoPapa関数の癖？かと思い気にしないで使用していました。

過去に紹介したコードがどのようなものだったかは
まったく思い出せませんが、課題ブックのVBAは、
エクセルが再計算を行うアルゴリズムにそぐわないコードなのかもしれません。

シート上にVBAで作成したオリジナルな関数式を配置し
更に、VBAの処理で値の書き換えや複写、コピペなどを行うと
アルゴリズムにそぐわない結果になるのかもしれません。

機会があれば、
課題ブックの全容を明らかにしてもらい、
処理全数を再構築したいところです。

お礼

毎度お手数をおかけします。
ご丁寧な提案で感謝！、
ですが本件は解決しましたのでBSにして締め切ったと思っておりました。

＞処理全数を再構築したいところです
実は、過去の質問の特定方法を知らないので日時しかわかりませんが、2021/7/26 16：01に下記のタイトルで質問しています。
「VBA関数がファイル開けるとエラーに再度の質問です」
これに対する最初のご回答を2021/7/27/ 22：48に頂いております。

結局原因不明でしたが、実用上問題なし、で一旦締め切っておりました。

今回は 新規Bookの作成時には1行目のコピペコードを追加したひな形（すでに作成済み）で、過去のBook（ザックリ３００？）については「上書き保存」で対応とします。

部署が変わり、新規の要求も出始めたのでそちらにHohoPaさんの時間を確保？？？しておきます。

いつもご丁寧な対応ありがとうございます。

補足

もし、過去の質問を検索されるとお手数をおかけすることになるので締め切り前にコピ張り付けておきます。

＞＞＞＞＞＞＞＞＞＞＞＞＞＞＞＞＞＞＞＞＞＞＞＞＞＞
VBA関数がファイル開けるとエラーに再度の質問です
2021/07/26 16:01
先日質問させていただいたのですが図を添付した方が分かり易いとのご指摘で再度投稿させていただきます。

下にデータが入っていきます。
添付では176個（LOT )のデータが17行目以降に入っています。
D４セルにはIFERROR（・・・・）を入れてみたら空白で表示されました。（当然？）

通常は担当者がセルにデータを入れると計算結果が（クルリと言う感じで）表示されるので実用上は問題無いのですが、最近このデータを要求されることが増えてきて、その時には最下行のセル（でなくてもどこでも）何かするとその列が計算されます。
ちなみにキャプチャー後に項目セル(D５）を削除してみても値が表示されました。（よって開けたまままの表です）
また、メニューの「数式」「再計算の実行」をいじってみましたが変化なし。（エラー表示のままでした）

コードは下記の通りです。
表にはAverage以外にMIN、MAX、STDEVをそれぞれ標準モジュールに同じ形式（コード）で仕込んであります。

Function sfAverage(Rng As Range, Optional bd) As Double

Dim LastRow As Long
Dim MyCol As Long
Dim tgRng As Range
Dim Border As Long
Dim StartRow As Long
Const DefBorder = 30

StartRow = 17 'データ開始行
If IsMissing(bd) Then
Border = DefBorder '省略された場合の閾値
Else
If ((bd = 0) Or (bd = "")) Then
Border = DefBorder '省略された場合の閾値
Else
Border = bd
End If
End If

MyCol = Rng.Column
LastRow = Cells(Rows.Count, MyCol).End(xlUp).Row
If LastRow <= StartRow Then
LastRow = StartRow
End If

'下から上方向に、数値となっているセルを探す
Do
If IsNumeric(Cells(LastRow, MyCol).Value) = True Then Exit Do
If LastRow <= StartRow Then Exit Do
LastRow = LastRow - 1
Loop

If LastRow > StartRow + Border - 1 Then
LastRow = LastRow - 1
StartRow = LastRow - Border + 1
End If

Debug.Print _
"開始行: " & StartRow & _
" / 終了行: " & LastRow & _
" / 対象列番号: " & MyCol

Set tgRng = Range(Cells(StartRow, MyCol), Cells(LastRow, MyCol))
sfAverage = WorksheetFunction.Average(tgRng)

End Function

画像を拡大する

回答No.7

HohoPapa

後記改修済みなコードを期待しているということでしょうか。

'再計算させるためのダミー処理
Sub sample()

　Dim wkData
　Dim c As Long

　For c = 1 To 20 '1列目から20列目までの1行目のセルを対象
　　wkData = Cells(1, c).Value
　　Cells(1, c).Value = wkData
　Next c

End Sub

お礼

毎回おんぶに抱っこで申し訳ありませんでした。
おかげさまで今回も早期解決できました。

1行目の数値貼りつけを最後に追加して解決できました。
実際には1行目にかかっている保護を外したり・・等の細かい問題がありましたが得意の試行錯誤で何とかかんとか。

思い起こせば、だいぶ前からファイルを開いた時には全ての列の計算結果がエラーになっている現象が有りました。

しかし、列にデータが入力されると計算結果が表示されるので、HohoPapa関数の癖？かと思い気にしないで使用していました。
気にしない、というより機能的に必須だったのでデメリットにならないという意味ですが。

今回VBAデータ入力すると逆に出ている計算結果がエラーになってしまうので困ってしまいました。

本当にありがとうございました。

回答No.6

HohoPapa

#5のコードでE4を指定したのは、単なる事例用のアドレスであって、
環境に合わせて書き換えてほしかったのです。

E4をどのセルアドレスに変えればいいかといいますと、
D4のセルに埋まっている計算式の中で計算元としているセルたちの中で
（計算式の埋まっていない）数値の埋まっているセルを選び
そのアドレスにしてほしいのです。

もし、
（計算式の埋まっていない）数値の埋まっているセルがないのであれば
返信してください。
計算式を上書きするコードを紹介します。

>①転記した数値をコピして数値貼り付け
>（転記した列ごとにｺコピペが必要だと思います）
>②上の動作を上記のコードに組み込む

②の組み込みを行ったコードを示すのはヤブサカではないものの
どのセル範囲なのかがわからない（覚えていない）ので
示すことができないのです。

お礼

お手数をおかけしております。
今 思いついたのですが、VBAでの転記先は必ず下記のように最下行なので、コードの最後に最下行全体をコピーして値で張り付ける。
なら、転記のデータ数に関係なく同じコードでいけるのでは？
素人考えですが。。。

.Sheets("入力表").Cells(Rows.Count, 4).End(xlUp)

補足

いつもながらお恥ずかしい限り・・・

各列の1行目は空白か品名、装置名等の文字列なので、先のご回答のE4を品名が入っているD1（転記先列）に変更してVBAを動かしたらバッチリでした。
一応D1セルを空白にしても確認して問題なく更新されたのですが、空白セルでもいいのでしょうか？

お手数ですが、転記先の複数列の1行目をコピペするコードをご教授頂きたく。

担当者が見つけた（手作業で）上書き保存すると更新されることが分かったので、ＶＢＡで上書き保存してみたのですがダメだったのですが、セルのコピペならVBAで実行しても更新されるということはHohoPapaさんの予想通りですね。

いつもながら感心しきり、です。
何卒よろしくお願いします。

最大4か所
Sub 計算表()
Dim MyRange As Range
With ThisWorkbook
Set MyRange = _
.Sheets("入力表").Cells(Rows.Count, 4).End(xlUp)
MyRange.Offset(1, 0).Value = .Sheets("計算表").Range("C11").Value
MyRange.Offset(1, 6).Value = .Sheets("計算表").Range("C20").Value
Application.Goto Reference:=Worksheets("入力表").Columns("A"), Scroll:=True
End With
End Sub

回答No.5

HohoPapa

セルに計算式が埋まっているときに、
計算式に影響のあるはセルが書き換わると
セルに埋まっている計算式が再計算されるわけですが

多くの計算式が多くのセルに点在しているときには、
再計算させるセルの順番をエクセルが専用のアルゴリズムで求めているそうです。
もし、このアルゴリズムに誤りが起きれば
条件によっては、正しく計算が行えずエラーになる可能性があるものと思います。

そこで、アテズッポウですが、
ポイントとなりそうなセルをVBAで書き換えれば、
計算式たちが期待通り再計算されるかもしれません。

私だったら、後記のようなVBAでセルの値を書き換え
期待した再計算が行われることを期待します。

Sub sample()
Dim wkData
With ThisWorkbook.Sheets("Sheet1")
wkData = .Range("E4").Value
.Range("E4").Value = wkData
End With
End Sub

補足

HohoPapa-さん
毎度お世話になります。
下記のコードは恐らくHohoPapaさんに教わったと思います。

今回のご回答の検証結果ですが、うまく解決できそうですが２つ問題があります。

うまく行きそうな根拠は、エラの出た状態（VBAで転記後）ご回答のコードを動かすと再計算されます。
但し、データは17行目以降であり、Ｅ４は統計関数の入ったセルなのでこの関数が数値に変わってしまいます。

つまり、転記されたセル＝転記先の列の最下行のセルの数値をコピして数値で張り付ければ解決できると思われます。

また実際には下記のように複数のセルを同時に飛ばしているシートもあります。（最大４か所）

Sub 計算表()
Dim MyRange As Range
With ThisWorkbook
Set MyRange = _
.Sheets("入力表").Cells(Rows.Count, 4).End(xlUp)
MyRange.Offset(1, 0).Value = .Sheets("計算表").Range("C11").Value
MyRange.Offset(1, 6).Value = .Sheets("計算表").Range("C20").Value
Application.Goto Reference:=Worksheets("入力表").Columns("A"), Scroll:=True
End With
End Sub

そこで毎度の追加のお願いですが、
①転記した数値をコピして数値貼り付け
（転記した列ごとにｺコピペが必要だと思います）
②上の動作を上記のコードに組み込む

現在は全ての計算シートが自動転記になっていますので、今更手入力には戻れないので何とかお願いします。

回答No.4

kon555

>>転記コードの最後に上書きを追記したが解消しない
　vbaでやれる事とすると、あとは更新させることと、一旦閉じて開き直すくらいですね。
　ただ開き直すのは正直なところ使いにくいので、再計算で解消しないなら「手動の上書き保存で更新する」という手順で良しとすべきかなと思います。

再計算についての参考
https://kokodane.com/mini_macro-65.htm

回答No.3

kon555

計算の更新でつっかえているような事象に思えますが、手動更新になったりしていませんか？
https://keizokuma.com/excel-recalc-auto/

　ちなみに私も類似事例の経験があり、vba側の入力だけ更新されない、というケースは稀にあります。もしかしたら仕様上の何かかもしれませんね。
　発生する場合、私はvba側で最後に上書き保存させたり、計算の更新を明示したりで回避してます。

お礼

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

当方の状況に類似の現象のように思います。
上書きで解消されるのも同じです。

VBAは得意ではないのでVBA自身に上書きさせることを思いつかなかったので、早速 転記コードの最後に上書き保存（ActiveWorkbook.Save）を追記してみたところ、転記後に上書き保存されていることは確認されましたが、不具合は改善されませんでした。
誠に残念！！

また、質問にも記載しましたが、過去にファイルを開いた時にはエラーでも列に数値を入れると計算されることがあり、その時にも計算方法の設定を確認して問題ないことを確認しています。（今回も設定は自動になっています）

今回は複数のBopokで同時多発の感じですのでエクセル側に何か意図しない変更が入ったのかもしれません。

回答No.2

3620313

決手段、改善の可能性のある方法に関して。

計算表シートのC11セルは、どの様に表示されてますか？
入力表シートで＃VALUE!となっているセルの中身は、どの様に表示されてますか？
下記で、どれかのセルの計算結果がNG（＃VALUE!)になってるのだと思われます。
D3,D4,D9,D10,D11,D12

まずは、＃VALUE！になっている要因から紐解いていけばと思います。

補足

早々のご回答ありがとうございます。

当方も最初は数値以外のものが転記されたのでは？と思い確認してみましたが、VBAで転記されて、エラー表示された列の下に同じ数値を手入力すると正しい値が表示されます。
というより上の行には過去のデータが空白なく埋まっているので飛んできた数値に問題があるハズですが、上記の現象が有るので飛んできた数値もおかしくないことになります。

また、先ほど担当者から上書き保存すると値が表示されるとのことで確認してみたら添付図の状態で上書き保存してみたら統計値が表示されます。

以前、ファイルを開いたときにはエラー表示でも、列のどこかに数値が入るとその列の統計値が表示されるのでそのまま使用していました。

全く不思議な現象です。

回答No.1

imogasi

この質問の添付写真だけでは、「列の上部の平均値や最大値」のデータ状況がよく理解できません。
もう少しデータ例を丁寧に挙げるべきです。
自分で新たに別データを作ってやってみるとどうなりますか。
この質問の問題はデータ性格などの問題でエラーが出るのでは、と思ったが？
関数的には、指定範囲内に文字列や空白セルがあっても、エラーにならないようです。
ーー
VBAはありがたいことに、エラーで、実行が止まったコード行に色を付けてくれます。
どこの行でエラーが起こりますか？エラーコードは？
下記やってみるとD列の最終行の直下にSheet2のセルC11の値を、追加してくれましたが。Sheet2のセルC11の値１つだけでよいのですか？全貌が良くわからない。
上部の関数の引数にどう影響するのですか？Sheet1のC列の最終行の辺りのデータが。
ーーー
下記で、コメントアウトした行は、テスト時に使っただけ。
Sub 計算表()
Dim MyRange As Range
With ThisWorkbook
Set MyRange = _
.Sheets("sheet1").Cells(Rows.Count, "d").End(xlUp)
'Sheets("Sheet1").Activate
'MyRange.Select
'MsgBox "AA"
MyRange.Offset(1, 0).Value = .Sheets("sheet2").Range("C11").Value
Application.Goto Reference:=Worksheets("Sheet1").Columns("A"), Scroll:=True
End With
End Sub

お礼

早々のご回答ありがとうございます。
問題はVBAではないと思います。
VBAは正しく最後まで動作し、ちゃんと計算表のC11に出力された値を指定のD列の最終セルに転記していますので。

また、その下に手入力で同じ数字を入力しても正しい統計値がポロンという感じで表示されます。
また、先ほど担当者から上書き保存すると値が表示されるとのことで確認してみたら統計値が表示されます。

以前、ファイルを平田ときにはエラー表示でも、数値が入るとその列の統計値が表示されるのでそのまま使用していました。