Excel (VBA)

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

 
(Windows 10 Pro : Excel 2016)
「値を貼り付け」の結果がVBAと手動で異なる件
投稿日時: 20/09/19 14:25:37
投稿者: ガイア

いつもお世話になっております。
 
数式をユーザーに見せたくないため、「値を貼り付け」して処理を終えたいのですが、VBAと手動とでは次のように結果が違います。
 
・VBA :3行目の値(0)が全てのセルに貼り付けられている
・手動:正しい値が全てのセルに貼り付けられている
 
なぜVBAでは手動と同様の正しい結果が得られないのかご助言を賜りたく、よろしくお願いいたします。
 
※以下、実際のコードです。
 
Public Function compKamoku()
    Dim tgtWs As Worksheet
    Dim i As Integer
    Dim j As Integer
    Dim iLim As Long
    Dim s As String
 
    Set tgtWs = sysWb.Worksheets("貼付用フォーマット")
    tgtWs.Select
 
    For j = 0 To 3 Step 1 '3行目の4項目に関数を貼り付ける
        Cells(3, 4 + j) = "=IFERROR(INDEX(会計データ!$A:$F,MATCH($A3,会計データ!$A:$A,0)," & 3 + j & "),0)"
    Next j
 
    Range("D3:G3").Select
    iLim = getRowLim(tgtWs, 3)
    Selection.AutoFill Destination:=Range("D3:G" & iLim) '3行目以降に関数をコピーする
     
    Range("D3:G" & iLim).Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
     
    Range("A1").Select
     
End Function

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

おそrく
> Cells(3, 4 + j) = "=IFERROR(INDEX(会計データ!$A:$F,MATCH($A3,会計データ!$A:$A,0)," & 3 + j & "),0)"
ここの数式が違っているような気がします。
 
ここで代入した数式を、イミディイトウィンドウにひょうじして、
手入力した数式と比較してみましょう。
 
Debiug.Pint Cells(3, 4 + j).Formula
 

投稿日時: 20/09/19 15:36:01
投稿者: ガイア

WinArrow さんの引用:
おそrく
> Cells(3, 4 + j) = "=IFERROR(INDEX(会計データ!$A:$F,MATCH($A3,会計データ!$A:$A,0)," & 3 + j & "),0)"
ここの数式が違っているような気がします。
 
ここで代入した数式を、イミディイトウィンドウにひょうじして、
手入力した数式と比較してみましょう。
 
Debiug.Pint Cells(3, 4 + j).Formula
 

 
 
WinArrowさん
 
早速のご回答ありがとうございます。
 
>> Cells(3, 4 + j) = "=IFERROR(INDEX(会計データ!$A:$F,MATCH($A3,会計データ!$A:$A,0)," & 3 + j & "),0)"
>ここの数式が違っているような気がします。
上記数式の箇所は、トレースの結果、この命令で正しい値が取得出来ておりましたので、正しいようです。
問題の箇所は、以下のステップでした。
このステップを通過すると、全てのセルに3行目の値が貼り付けられてしまいます。
 
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
 
 
しかし、VBAでなく手動で「コピー」して「値を貼り付け」すると、正しく貼り付けられます。
引き続き、ご指導いただきますようお願いいたします。
 

回答
投稿日時: 20/09/19 16:22:08
投稿者: WinArrow
投稿者のウェブサイトに移動

ガイア さんの引用:

問題の箇所は、以下のステップでした。
このステップを通過すると、全てのセルに3行目の値が貼り付けられてしまいます。
 
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
 
 
しかし、VBAでなく手動で「コピー」して「値を貼り付け」すると、正しく貼り付けられます。
引き続き、ご指導いただきますようお願いいたします。
 

 
こちらで、簡易的にエータを作成して
実行してみましたが、問題はありません。
 
>getRowLim(tgtWs, 3)
の処理は掲示されていませんが、問題ないのでしょうか?

回答
投稿日時: 20/09/19 17:12:11
投稿者: simple

横入り失礼。
    For j = 0 To 3 Step 1 '3行目の4項目に関数を貼り付ける
        Cells(3, 4 + j) = "=IFERROR(INDEX(会計データ!$A:$F,MATCH($A3,会計データ!$A:$A,0)," & 3 + j & "),0)"
    Next j
    STOP
と一行追加してみて、そのとき3行目はどうなっていますか?
0になっていませんか?

投稿日時: 20/09/19 17:12:44
投稿者: ガイア

WinArrow さんの引用:
ガイア さんの引用:

問題の箇所は、以下のステップでした。
このステップを通過すると、全てのセルに3行目の値が貼り付けられてしまいます。
 
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
 
 
しかし、VBAでなく手動で「コピー」して「値を貼り付け」すると、正しく貼り付けられます。
引き続き、ご指導いただきますようお願いいたします。
 

 
こちらで、簡易的にエータを作成して
実行してみましたが、問題はありません。
 
>getRowLim(tgtWs, 3)
の処理は掲示されていませんが、問題ないのでしょうか?

 
 
WinArrowさん
 
ご検証およびご回答いただきありがとうございます。
問題ないとのこと、承知いたしました。
 
getRowLimの処理を以下に記しますが、こちらは汎用サブルーチンのため問題ないと思われます。
引き続きよろしくお願いいたします。
 
 
'名 前:getRowLim
'概 要:指定したシート内の指定した列でデータが入力されている最終行を探す
'パラメータ:tgtWs→指定するシート
' :tgtCol(省略の場合はA列)→指定する列
'戻り値:データが入力されている最終行番号(データが入力されているセルが無い場合は『0』)
 
Public Function getRowLim(ByVal tgtWs As Worksheet, Optional tgtCol As Long = 1) As Long
    getRowLim = 0
    With tgtWs
        getRowLim = .Cells(.Rows.Count, tgtCol).End(xlUp).row
    End With
End Function
 
 

投稿日時: 20/09/19 17:18:15
投稿者: ガイア

simple さんの引用:
横入り失礼。
    For j = 0 To 3 Step 1 '3行目の4項目に関数を貼り付ける
        Cells(3, 4 + j) = "=IFERROR(INDEX(会計データ!$A:$F,MATCH($A3,会計データ!$A:$A,0)," & 3 + j & "),0)"
    Next j
    STOP
と一行追加してみて、そのとき3行目はどうなっていますか?
0になっていませんか?

 
simpleさん
 
ご回答いただきありがとうございます。
 
3行目には、「=IFERROR(INDEX(会計データ!$A:$F,MATCH($A3,会計データ!$A:$A,0),3),0)」という関数がセットされております。
表示されている値は「0」です。
 
以上、よろしくお願いいたします。

回答
投稿日時: 20/09/19 17:22:57
投稿者: WinArrow
投稿者のウェブサイトに移動

>getRowLim(tgtWs, 3)
の件、了解しましたが、
列指定が「3」=C列でよいのでしょうか?

回答
投稿日時: 20/09/19 17:27:19
投稿者: simple

そうであれば、
それを増幅して、値貼付したらすべてに0という値が貼り付けられるのは、
ごく自然のことのように思いますが。
質問はどういうことでしょうか? もう一度説明していただけますか?

投稿日時: 20/09/19 17:32:25
投稿者: ガイア

WinArrow さんの引用:
>getRowLim(tgtWs, 3)
の件、了解しましたが、
列指定が「3」=C列でよいのでしょうか?

 
WinArrowさん
 
ご回答ありがとうございます。
1列目、2列目とも、途中に空白セルがあるため、最終行まで空白セルのない3行目を指定しております。
 
以上、よろしくお願いいたします。

投稿日時: 20/09/19 17:36:33
投稿者: ガイア

simple さんの引用:
そうであれば、
それを増幅して、値貼付したらすべてに0という値が貼り付けられるのは、
ごく自然のことのように思いますが。
質問はどういうことでしょうか? もう一度説明していただけますか?

 
simpleさん
 
ご回答ありがとうございます。
質問を再掲します。
以上、よろしくお願いいたします。
 
=================
 
数式をユーザーに見せたくないため、「値を貼り付け」して処理を終えたいのですが、VBAと手動とでは次のように結果が違います。
  
・VBA :3行目の値(0)が全てのセルに貼り付けられている
・手動:正しい値が全てのセルに貼り付けられている
  
なぜVBAでは手動と同様の正しい結果が得られないのかご助言を賜りたく、よろしくお願いいたします。
  
※以下、実際のコードです。
  
Public Function compKamoku()
    Dim tgtWs As Worksheet
    Dim i As Integer
    Dim j As Integer
    Dim iLim As Long
    Dim s As String
  
    Set tgtWs = sysWb.Worksheets("貼付用フォーマット")
    tgtWs.Select
  
    For j = 0 To 3 Step 1 '3行目の4項目に関数を貼り付ける
        Cells(3, 4 + j) = "=IFERROR(INDEX(会計データ!$A:$F,MATCH($A3,会計データ!$A:$A,0)," & 3 + j & "),0)"
    Next j
  
    Range("D3:G3").Select
    iLim = getRowLim(tgtWs, 3)
    Selection.AutoFill Destination:=Range("D3:G" & iLim) '3行目以降に関数をコピーする
      
    Range("D3:G" & iLim).Select '←関数が入っている範囲を指定する
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False '←これを実行すると、全てのセルが3行目の値で置き換わってしまう
      
    Range("A1").Select
      
End Function

回答
投稿日時: 20/09/19 17:41:37
投稿者: WinArrow
投稿者のウェブサイトに移動

回答者の環境で再現できないので、
原因は、分かりません。
 
代案のコードを紹介します。
 

引用:
Range("D3:G" & iLim).Select
     Selection.Copy
     Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
         :=False, Transpose:=False

の代案として
 
    With Range("D3:G" & iLim)
        .Value = .Value
    End With
 

回答
投稿日時: 20/09/19 17:44:58
投稿者: simple

値が0なら3行目の0も4行目の0も区別つかないのは当然です。
質問の趣旨がわかりませんね。

投稿日時: 20/09/19 17:52:21
投稿者: ガイア

WinArrow さんの引用:
回答者の環境で再現できないので、
原因は、分かりません。
 
代案のコードを紹介します。
 
引用:
Range("D3:G" & iLim).Select
     Selection.Copy
     Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
         :=False, Transpose:=False

の代案として
 
    With Range("D3:G" & iLim)
        .Value = .Value
    End With
 

 
WinArrowさん
 
ご回答ありがとうございます。
残念ながらご提示いただいたロジックでも解決できませんでした。
 
しかし、WinArrowさんの環境では再現できない、とのことでしたので、ロジックには問題ないのだろうと思います。
ロジックとは別の部分を調査しようと思います。
 
色々とアドバイス頂きありがとうございました。