Excel (VBA)

Excel VBAに関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 10全般 : Excel 2016)
値貼り付け後データが消える?
投稿日時: 19/06/27 11:30:21
投稿者: vaioyuki

別シートの最終行を取得
https://www.moug.net/faq/viewtopic.php?t=78368
 
別シートの最終行を取得からの続き
https://www.moug.net/faq/viewtopic.php?t=78374
 
別シートの最終行を取得 の vaioyukiさんへ
https://www.moug.net/faq/viewtopic.php?t=78372
 
 
長々とお世話になっており、理解力もなく申し訳ありません。(T-T)
 
今していること、解明できたことを順序だてていくと、
ブックには4つのシートがあり、
・Sheet1(集計) *Suzuさまの質問通り、オブジェクト名としてSheet1という名前になっています
・Sht_csv(パターンC)
・Sht_dwh(DWH)
・Sht_macro(マクロ)
となっていて、
・function
というモジュールがあります。
 
Sht_macro(マクロ)シートにボタンがあり、このボタンを押すと保存先を選択してからDWH(xlsx)とパターンC(csv)を読み込むフォームが開き、各シートにコピーされ、Sheet1(集計)のセルに必要なデータのみ計算式で集計されます。
DWHは鍵付きですのでパスワード入力後、開いたままの状態になります。
シートの選択でつまずいたのは、集計シートにはうまく集まったのでが合計を出すときに開かれた別ブックDWHの最終行に合計欄が入力されました。
こちらは下手な構文だとは思いますがwithを入れ子出来るということを知って、
 

With ThisWorkbook.Worksheets("集計") 'パターンCシートの最終行まで集計シートに計算式を自動入力

    .Range("A" & lngFirstFormuraRow & ":A" & lngLastFormuraRow).Formula = "=Match(Substitute($g" & lngFirstFormuraRow & ",""-"",""""),DWH!$E:$E,0)"

〜〜〜〜〜〜〜〜〜〜 省 略 〜〜〜〜〜〜〜〜〜〜

    .Range("AD" & lngFirstFormuraRow & ":AD" & lngLastFormuraRow).Formula = "=IFERROR(OFFSET(DWH!$T$1,集計!$A3-1,0),"""")"
    
        With ThisWorkbook.Worksheets("集計").Range("J" & Goukei) '合計を最終行に追加

            .Formula = "=SUM(J3:J" & (Goukei - 1) & ")"
            .AutoFill Destination:=.Resize(1, 20)

                With ThisWorkbook.Worksheets("集計").Range("B3:AD" & Goukei) '全てに格子線を引く

                    .Borders.LineStyle = xlContinuous
       
                End With
        End With

 
集計シートに計算式を入力する文の後に入れると無事に合計が出来て罫線も引くことが出来ました。
 
 
長々と前置きしましたがここからが本題です。(^^;)
 
最終的には集計シートを新しいブックにコピーしてお客様にお渡ししています。
 
Sheet1.copy 'シートを新しいブックにコピーする

    Range("B:AD").copy
    Range("B1").PasteSpecial Paste:=xlPasteValues '値の貼り付け
    Application.CutCopyMode = False 'コピーモード解除

With ActiveSheet
    .Columns(1).Delete '1列目を削除
    Range("A1").Select
End With

If Mdl_function.saveUniqueFile(Sht_macro.Str_savepath, _
  "料金レポート" & Format$(Dat_riyou, "(yyyy年M月分)"), ".xlsx") = "" Then '一意な名前を付けて保存
  MsgBox "ファイルの保存に失敗しました"
  Application.Calculation = xlAutomatic '再計算、自動
  Application.ScreenUpdating = True '画面更新、オン
  Exit Sub '帰る
End If
Sht_macro.Activate 'マクロシートをアクティブ
Application.Calculation = xlAutomatic '再計算、自動
Application.ScreenUpdating = True '画面更新、オン
MsgBox "処理完了"

 
出来たブックを見ると、集計シートには入力されているのに新しいブックには入力されていないものがあります。
 
.Range("F" & lngFirstFormuraRow & ":F" & lngLastFormuraRow).Formula = "=IFERROR(TEXT(OFFSET(DWH!$E$1,集計!$A3-1,0),""000-0000-0000""),"""")"
 
上記の式が入力されているセルです。
ちなみに参照しているA列には下記の式が入力されています。
 
.Range("A" & lngFirstFormuraRow & ":A" & lngLastFormuraRow).Formula = "=Match(Substitute($g" & lngFirstFormuraRow & ",""-"",""""),DWH!$E:$E,0)"
 
このA列は最終で削除しています。
 
値貼り付けにしなくても、
A列を削除しなくても、
集計!$A3を$A3にしても、
なぜかこの列のデータだけ消えています。
 
何かほかに考えられることはありますか?
 
よろしくお願いします。

回答
投稿日時: 19/06/27 15:27:04
投稿者: WinArrow
投稿者のウェブサイトに移動

ちょっと見で、外しているかもしれませんが
マクロはシートモジュールい記述していますか?
 
もしそうだとしたら、
シートモジュールに記述したコードの中で、
他のシートを参照しているところがあると、
意図せぬ結果になることがあります。
シートモジュールのコードでは、他シートは参照でいないと考えた方がよいでしょう。
基本的に
複数のシートを取り扱う場合は、標準モジュールにコードを記述します。

回答
投稿日時: 19/06/27 15:40:00
投稿者: sk

引用:
・Sheet1(集計)

引用:
.Range("F" & lngFirstFormuraRow & ":F" & lngLastFormuraRow).Formula = "=IFERROR(TEXT(OFFSET(DWH!$E$1,集計!$A3-1,0),""000-0000-0000""),"""")"

引用:
このA列は最終で削除しています。

引用:
Sheet1.copy 'シートを新しいブックにコピーする

引用:
With ActiveSheet
    .Columns(1).Delete '1列目を削除
    Range("A1").Select
End With

引用:
A列を削除しなくても、
集計!$A3を$A3にしても、
なぜかこの列のデータだけ消えています

OFFSET 関数の中で絶対参照している列/セルが削除されれば、
#REF! エラーが発生した結果としてそうなるのは当然。
 
引用:
値貼り付けにしなくても

引用:
Range("B:AD").copy
Range("B1").PasteSpecial Paste:=xlPasteValues '値の貼り付け
Application.CutCopyMode = False 'コピーモード解除

引用:
Range("A1").Select

これらのコードがシートモジュールに記述されているものなのであれば、
上記赤字箇所において参照されているセルは
「アクティブシートのセル」ではなく
「そのシートモジュールが埋め込まれている
ワークシート自身のセル」です。
 
つまり「ワークシート[マクロ]のセル範囲 B:AD をクリップボードにコピーして
ワークシート[マクロ]の B1 セルに値貼り付けを行なっている」という処理を
実行しているに過ぎず、アクティブシート([集計]をコピーしたワークシート)に対しては
「アクティブシートの A 列を削除する」という操作しか行なわれておらず、
アクティブシートの数式セルはいまだに数式セルのままであるということ。

投稿日時: 19/06/28 14:47:40
投稿者: vaioyuki

お返事が遅くなり申し訳ありません。
 
わかったようなわからないような不思議な感じです。(^^;)
 

引用:
つまり「ワークシート[マクロ]のセル範囲 B:AD をクリップボードにコピーして
ワークシート[マクロ]の B1 セルに値貼り付けを行なっている」という処理を
実行しているに過ぎず、アクティブシート([集計]をコピーしたワークシート)に対しては
「アクティブシートの A 列を削除する」という操作しか行なわれておらず、
アクティブシートの数式セルはいまだに数式セルのままであるということ。

 
確かに、
何度か繰り返していろんなことをしているときにA列が全て#N/Aになったことがあります。
そして
=IFERROR(TEXT(OFFSET(DWH!$E$1,#REF!-1,0),"000-0000-0000"),"")
みたいなのになったのを見た記憶もうっすらあります。(^^;)
アクティブシートになっていないというのは、
 
With ActiveSheet
    .Columns(1).Delete '1列目を削除
 
こちらでActiveSheetを選択しているから?
 
上記より30分ほど経ちまして、変更しましたw
 
Sheet1.copy 'シートを新しいブックにコピーする

With ActiveSheet

  .Calculate '再計算
    Range("B:AD").copy
    Range("B1").PasteSpecial Paste:=xlPasteValues '値の貼り付け
    Application.CutCopyMode = False 'コピーモード解除

    .Columns(1).Delete '1列目を削除
    Range("A1").Select
End With

 
再計算を入れたら無事に表示出来ました!!
最初は
With ActiveSheet
の位置を上にあげたのですがこれだけでは表示せず、
  .Calculate '再計算
を入れると動きました。
この .Calculate は元の集計シートに計算式が入力されていたのでそれの再計算なのかなと思い外していました。
でもVBAで式を入力しても元からセルに直接式が入力されていたとしても、
どちらにしろ必要な 再計算 だったということなんですね。
 
 
ありがとうございました!!