[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『計算式が勝手に代わるのはなぜ』（政彊7878）
いつもお世話になっております。
今回は、入力してある計算式が、ﾌｧｲﾙ開くたび代わる現象が発生しています。
IFERROR(VLOOKUP(B2,'L:\所属\関連\関連原紙\[購入照会ﾍﾞｰｽ.xlsm]購入照会ﾍﾞｰｽ'!$B$1:$C$200,2,FALSE),"申請中")という数式なのですが、
ﾌｧｲﾙ保存後の翌日に開くと
\[購入照会ﾍﾞｰｽ.xlsm]購入照会ﾍﾞｰｽ'!$B$1:$C$200,2,FALSE)が
\[購入照会ﾍﾞｰｽ.xlsm]購入照会ﾍﾞｰｽ'!$C$1:$C$190,2,FALSE)に
代わっていて計算値が表示されない現象が発生します。
何が原因なのでしょうか。
又、照合元の\[購入照会ﾍﾞｰｽ.xlsm]購入照会ﾍﾞｰｽ'!が
\[購入照会ﾍﾞｰｽ.xlsm]#REF! になっているときもあります。
照合元にはﾏｸﾛﾃﾞｰﾀｰが入っていて、ｼｰﾄ内の番号変更（短縮）作業
=CONCATENATE((LEFT(A1,5)),(RIGHT(A1,4)))と並替え作業をさせてます。
以下ﾏｸﾛ
Sub 並替え()
'
' 並替え Macro
'
'
Sheets("購入照会ﾍﾞｰｽ").Select
Rows("1:10").Select
Selection.Delete Shift:=xlUp
Range("D:D,G:G,L:M").Select
Range("L65").Activate
Selection.Delete Shift:=xlToLeft
Range("L8").Select
ActiveWindow.ScrollColumn = 22
Columns("AE:AE").Select
Selection.Copy
Columns("B:B").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("B:B").Select
Application.CutCopyMode = False
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Sheets("手順書").Select
Range("J6:J7").Select
Selection.Copy
Sheets("購入照会ﾍﾞｰｽ").Select
Range("B1").Select
ActiveSheet.Paste
Range("B2").Select
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("B2:B200"), Type:=xlFillDefault
Range("B2:B200").Select
Range("A1:AS1").Select
Selection.AutoFilter
ActiveWorkbook.Worksheets("購入照会ﾍﾞｰｽ").AutoFilter.Sort.SortFields.Clear
ActiveWorkbook.Worksheets("購入照会ﾍﾞｰｽ").AutoFilter.Sort.SortFields.Add Key:= _
Range("A1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("購入照会ﾍﾞｰｽ").AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
Selection.AutoFilter
Sheets("購入照会ﾍﾞｰｽ").Select
Columns("B:B").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
With Selection
.HorizontalAlignment = xlGeneral
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With Selection
.HorizontalAlignment = xlCenter
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("A1").Select
End With
End Sub
＜ 使用 Excel：Excel2016、使用 OS：Windows10 ＞
ﾌｧｲﾙ保存後の翌日に開くと \[購入照会ﾍﾞｰｽ.xlsm]購入照会ﾍﾞｰｽ'!$B$1:$C$200,2,FALSE)が \[購入照会ﾍﾞｰｽ.xlsm]購入照会ﾍﾞｰｽ'!$C$1:$C$190,2,FALSE)に 代わっていて計算値が表示されない現象が発生します。 何が原因なのでしょうか。
たぶん…
ここで10行削除しているので$C$200が$C$190になる
Sheets("購入照会ﾍﾞｰｽ").Select
☆Rows("1:10").Select
☆Selection.Delete Shift:=xlUp
ここで列の挿入をしているので$B$1が$C$1になる
☆Columns("B:B").Select
Application.CutCopyMode = False
☆Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
ということかなと(^^; （虎） 2018/06/15(金) 09:55
10行削除分を追加してやるには具体的に、どうすれば？
1列挿入分を減らすには？どうすれば？
ちなみに、このﾏｸﾛはオートで作っているので、ﾏｸﾛ詳しくないです。
別のﾌｧｲﾙで作られたﾏｸﾛが、ﾘﾝｸ先に影響するのが、よく解りませんが
直す方法があれば御教授お願いします。
（政彊7878） 2018/06/15(金) 11:01
自分もマクロ初心者なので、原因のあたりはつけられても具体的にどうすれば…っていうのは ちょっと思いつかないです…すみません…orz
他の方がよい方法を教えてくださるといいのですが… 何も思いつかないかもしれないですが、一応自分でも考えてみます…(^^; （虎） 2018/06/15(金) 11:12
もしかしたら、10行削除しても1列挿入しても数式の内容が変わってしまうことには 直接関係ないかもしれないです…すみません…orz （虎） 2018/06/15(金) 11:21
どなたか助けてください。
（政彊7878） 2018/06/15(金) 12:50
\[購入照会ﾍﾞｰｽ.xlsm]購入照会ﾍﾞｰｽ'!$B$1:$C$200,2,FALSE)が
\[購入照会ﾍﾞｰｽ.xlsm]購入照会ﾍﾞｰｽ'!$B$1:$C$190,2,FALSE)に
代わっていて計算値が表示されない現象が発生します。
購入照会ﾍﾞｰｽ.xlsm内に同様の計算式をいれておいたら、
IFERROR(VLOOKUP(B2,購入照会ﾍﾞｰｽ!$B$1:$C$200,2,FALSE),"申請中")が
IFERROR(VLOOKUP(B2,購入照会ﾍﾞｰｽ!$C$1:$C$190,2,FALSE),"申請中")に
なってます。
どなたか助けてください。
（政彊7878） 2018/06/15(金) 15:31
>ﾌｧｲﾙ保存後の翌日に開くと >\[購入照会ﾍﾞｰｽ.xlsm]購入照会ﾍﾞｰｽ'!$B$1:$C$200,2,FALSE)が >\[購入照会ﾍﾞｰｽ.xlsm]購入照会ﾍﾞｰｽ'!$C$1:$C$190,2,FALSE)に >代わっていて計算値が表示されない現象が発生します。
翌日？？？？ 単純にマクロ実行後では？
賛否両論あるだろうけど
INDIRECT("購入照会ﾍﾞｰｽ!$B$1:$C$200")
因みに購入照会ﾍﾞｰｽの入ったシートがあるブックが開かれて無いとエラーになります。 （BJ） 2018/06/15(金) 16:14
とりあえずコードが見づらかったので整理してみました。
データ無いからテストはできないですけど、コンパイルエラーにならないので、記述的にはだいたいあってるハズ。
Sub 並替え改()
With Sheets("購入照会ヘ゛ース")
'不要行列の削除（[Shift]は列(行）まるごとなので省略）
.Rows("1:10").Delete
.Range("D:D,G:G,L:M").Delete
'B列にAE列の値だけ複写
Intersect(.UsedRange.EntireRow, .Columns("B:B")).Value = _
Intersect(.UsedRange.EntireRow, .Columns("AE:AE")).Value
'B列の位置に１列挿入（[Shift]は列まるごとなので省略、[CopyOrigin]は規定値なので書略）
.Columns("B:B").Insert
'「手順書」の「J6:J7」を「購入照会ヘ゛ース」の「B1:B2」にコピペ
Sheets("手順書").Range("J6:J7").Copy .Range("B1")
'「B2」の値を「B200」までオートフィルでコピー([Type]は規定値なので省略)
.Range("B2").AutoFill Destination:=Range("B2:B200")
'--------------
With Intersect(.Range("A1:AS1").CurrentRegion, .Columns("A:AS"))
With .Sort '（[SortMethod]は規定値なので省略）
.SortFields.Clear
.SortFields.Add Key:=Range("A1") '([SortOn],[Order],[DataOption]いずれも規定値？なので省略)
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.Apply 'ソート実行
End With
End With
With Intersect(.UsedRange.EntireRow, .Columns("B:B"))
.Value = .Value 'B列の値をB列に入れて値貼付の代わり
'なんか無駄に書式設定してるのでまるっと削除（2回目のほうだけ残してます）
.HorizontalAlignment = xlCenter
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
'--------------
Application.Goto .Range("A1")
End With
End Sub
これをみると、読み間違えていたらごめんなさいですが
同じブックの中の話のように見える（ブックを切り替えてる形跡が無い）ので、「購入照会部ベース」シートの要らない行列の削除等をしてから自ブックの「手順」シートから数式がはいったセルをコピーしてきて、オートフィルでB2〜B200まで数式をコピーして、それを値に直してるマクロじゃないですか？
その上で、問題の数式が入ってるブックが参照している【購入照会ﾍﾞｰｽ.xlsm】に１列挿入しているのだから（参照先の変化に追従して）ずれるのは正常な動作のような気がします。
ちょっと自信が無いですけど、おそらく数式がずれて困っているブックを閉じた状態で（開いてないときに）、マクロを動かせば数式は変わらないと思います。
（というか、閉じてるから追従して数式を変更できない。）
（※外部参照になってるので、もしかしたら考え違いかもしれないので、他の回答者さんの意見も参考にしてください。）
ちなみに、コードの方が気になるんですが、元のコードだと並び替えをする範囲を特定するために、オートフィルタを設定してるようですけど、CurrentRegionプロパティ使ってうまく処理できないでしょうか？
ダメじゃ無いけど、うっかり「購入照会ベース」シートにオートフィルタを設定したまま、マクロを動かしたら最初の.AutoFilterが解除っていう意味になって、エラーになるんじゃないかとおもうんですが・・
（もこな２） 2018/06/15(金) 18:26
（１）問題が生じているブックに「取込」というシートを追加。
（２）数式を以下に修正。
=IFERROR(VLOOKUP(B2,取込!$A$1:$B$200,2,FALSE),"申請中")
（３）並替えマクロの最後に以下を追加
Workbooks("問題のブック").Worksheets("取込").Range("A1:B200").Value = _
Sheets("購入照会ﾍﾞｰｽ").Range("B1:C200").Value
としてみてはどうでしょうか。
要は、他ブックを参照するんじゃなくて、マクロで必要なデータを持ってくるようにしたらよいかと思います。
（もこな２） 2018/06/15(金) 19:52
行を削除しているから、 それに応じて数式が変わるだけなんじゃないですか？ 面倒くさいのでコード全く読んで無いけど。 （BJ） 2018/06/15(金) 20:17
対策としては、
数式側で影響の無いように工夫する。
又は、
マクロで工夫する。
どっちでも対応できると思います。
数式側で対応するなら。
1）既に出ている
INDIRECT("購入照会ﾍﾞｰｽ!$B$1:$C$200")
とセル範囲を数式の中で文字列で指定するのもあると思いますが、
2）
名前の定義を使って対象範囲を可変で取得できるようにするとか、
計算で定型になるように定義することも出来るとは思います。
3）
それから、単に新しく数式を入力するのもあると思います。
１つ直したらあとはフィルハンドルをダブルクリックで数式を必要な範囲にコピー
出来るので、それがとりあえずストレスが少ないかなと思います。
https://www.tschoolbank.com/excel/calculatie-auto-fill/
4）
あ、あと、数式も文字の置換の対象にできるので、
そちらで一括で置き換えるのもありそうです。
マクロ側で対応するなら、
1）
数式が変化しないように、その数式があるセル範囲の数式の頭に
シングルクォーテーションを付けて、一時的に強制的に文字列に変化させておいてから、
行削除をしてから、数式を戻すなんてことも出来そうですが、
2）
マクロついでに、置換をマクロでやっちゃうとか、
3）
新規で数式を一括で入れてしまうとかしちゃえばいいと思います。
個人的には、
数式で参照しておいて、「こっちが代わればあっちはなにをしなくても自動で変わるはず。」
という他力本願的なマクロを作るのは好みではありません。
出来れば、データ元を編集したなら、変わる結果もその都度更新するところまで、
マクロで面倒をみてあげて欲しいなと思いますが、
そこはいろいろな意見があると思います。
（まっつわん） 2018/06/16(土) 11:37
どなたか助けてください。
（政彊7878） 2018/06/15(金) 12:50
どなたか助けてください。
（政彊7878） 2018/06/15(金) 15:31
と焦っている（ように見える）割には、BJさんのアドバイス以降レスがないのはどうなのかな〜とおもったり。
とりあえず、マクロで
D、G、L、Mの各列を削除 ←数式が参照してるB、C列には影響無し 1〜10行目を削除 ←数式が参照している1〜200行目に含まれてるので影響が出る
なので、お行儀がわるいかもしれないけど、↓のように列全体を参照するように変えてみたらどうでしょう？
IFERROR(VLOOKUP(B2,購入照会ﾍﾞｰｽ!$B:$C,2,FALSE),"申請中")
（もこな２） 2018/06/16(土) 12:39
週末用事ありまして仕事早く上がらなければならず回答が遅れました。
現状は参照先の元データーを変えず変更先でﾃﾞｰﾀー行の修正をし、対応してました。
選択肢が色々あるのですね、これから一つずつ実施してみようと思います。
結果、出ましたらまた連絡入れさせていただきます。
（政彊7878） 2018/06/18(月) 06:31
問題発見後は、ﾏｸﾛ起動後になります。
計算式が変わるﾌｧｲﾙｼｰﾄは、ほぼ常設なので1日開いたままです。
それ以外で、
元々はExcelに置き換えたﾌｧｲﾙが有り、そのｼｰﾄをそっくりｺﾋﾟｰして、購入照会ベース(ﾏｸﾛ入り）ﾌｧｲﾙの
Sheets("購入照会ヘ゛ース")に貼り付けます。
それを Sheets("手順書")にあるﾏｸﾛ起動ﾎﾞﾀﾝでﾏｸﾛ起動させる。
ﾏｸﾛは正常に起動してSheets("購入照会ヘ゛ース")内で、求めたい値にして並び替えを実施している。
ﾏｸﾛ自体にはやりたいことが出来ているので不満はないのですが、購入照会ベース(ﾏｸﾛ入り）ﾌｧｲﾙ
に参照しに行く、”計算式が変わるﾌｧｲﾙｼｰﾄ”が変わる原因が理解できませんでした。
それも行だけでなく、列も追従して変わるのですね。
ではﾏｸﾛ実行Sheets("購入照会ヘ゛ース")で参照をせず
参照用sheetを追加転記して、そのデータを参照すれば良いのですね。
IFERROR(VLOOKUP(B2,購入照会ﾍﾞｰｽ!$B$1:$C$200,2,FALSE),"申請中")を
IFERROR(VLOOKUP(B2,参照ﾍﾞｰｽ!$B$1:$C$200,2,FALSE),"申請中")
に変更し実行した所、問題なく表示され計算式も変わりませんでした。
ﾌｧｲﾙを開いてても、閉じていても、参照ﾌｧｲﾙが変わることはないし、計算式にも影響なし
なのでこれがベストと判断しました。
皆様、いろいろとご指導いただきありがとうございます。
（政彊7878） 2018/06/18(月) 11:16
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.