[[20210531125104]]　『VBAでのOffset使用について』（ミシェル）　ページの最後に飛ぶ

『VBAでのOffset使用について』（ミシェル）

ファイル「C.xlsm」のシート「D」のセル「B2:AM2」の値の中にファイル「A.xlsm」のシート「B」のセル「C4」と同じ値がある場合、セル「B2:AM2」と合致するセルの３つ下に数式「VLOOKUP(B5,'[A.xlsm]B'!$A$4:$C$300,3,0)」を入れるというものを作りたいのですが、MyR～の行で実行時エラー438「オブジェクトは、このプロパティまたはメソッドをサポートしていません」というエラーとなってしまいます。
誤っている箇所がお分かりでしたらご指摘頂けますでしょうか？

Dim MyR As Range
With Range("B2:AM2").Select
　　For Each MyR In Selection
　　If Workbooks"A.xlsm").Worksheets("B").Range("C4") = MyR Then
　　MyR.Cells.Offset(3, 0).Formura = "VLOOKUP(B5,'[A.xlsm]B'!$A$4:$C$300,3,0)"
　　End If
　　Next MyR
End With

＜使用 Excel：Office365、使用 OS：Windows10 ＞

MyR.Cells.Offset(3, 0) = "=VLOOKUP(B5,'[A.xlsm]B'!$A$4:$C$300,3,0)"

でうごきませんか？
（no_name） 2021/05/31(月) 13:29

所定の位置に入ったのですが、数式の文字列がそのまま入ってしまいました・・・
数式の値を入れたいのですが、Formuraの使い方は間違っていますでしょうか？
（ミシェル） 2021/05/31(月) 13:59

入力しようとしているセルの書式が文字列になっていませんか？

私は書式：標準のセルに数式を説明したとおりに入れた動きました。

Formuraは数式を取得する場合に使うものではなかったでしょうか？
（no_name） 2021/05/31(月) 14:24

 ＞MyR.Cells.Offset(3, 0).Formura =  "VLOOKUP(B5,'[A.xlsm]B'!$A$4:$C$300,3,0)"
   MyR.Cells.Offset(3, 0).Formula = "=VLOOKUP(B5,'[A.xlsm]B'!$A$4:$C$300,3,0)"

 Formura  →  Formula
 VLOOKUP　→ =VLOOKUP

（半平太） 2021/05/31(月) 14:50

動きました、ありがとうございます。

この数式を最終行までコピペして＃N/Aを空欄にしたいのですが、
誤っている箇所お分かりでしたら教えて頂けますでしょうか？
（ActiveCell.Copy Paste = xlPasteValues）のところでエラーとなってしまいます。

Dim MyR As Range

 With Range("B2:AM2").Select 
　　For Each MyR In Selection 
　　If Workbooks"A.xlsm").Worksheets("B").Range("C4") = MyR Then 
　　MyR.Cells.Offset(3, 0) = "=VLOOKUP(B5,'[A.xlsm]B'!$A$4:$C$300,3,0)"
    ActiveCell.FillDown
    ActiveCell.Copy Paste = xlPasteValues
    Selection.Replace What:="#N/A", Replacement:="", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2 
　　End If 
　　Next MyR 
 End With

（ミシェル） 2021/05/31(月) 15:34

＞With Range("B2:AM2").Select
.Selectいらない
＞If Workbooks"A.xlsm").Worksheets("B").Range("C4") = MyR Then
workbooksの後にカッコつけ忘れてる
＞MyR.Cells.Offset(3, 0) = "=VLOOKUP(B5,'[A.xlsm]B'!$A$4:$C$300,3,0)"
MyRはRangeオブジェクトなので.Cellsはいらない。Formulaがない
MyR.Offset(3, 0).Formula　= "=VLOOKUP(B5,'[A.xlsm]B'!$A$4:$C$300,3,0)"
＞ActiveCell.FillDown
Selection.FillDownでは？
＞ActiveCell.Copy Paste = xlPasteValues
Selection.Copyでは？
Copyメソッドの引数は貼り付け先のみです。値のみ貼り付けの場合はPasteSpecialメソッドです
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutcopyMode = False

（砂糖） 2021/05/31(月) 15:51

色々書いたけど範囲選択をしてから実行するのかと思ったらそうでもないのかな？
B2:AM2固定なら
＞Selection.FillDown
＞Selection.Copy
は
.FillDown
.Copy
です
＞Selection.Replace What:="#N/A", Replacement:="", LookAt:=xlPart, _
も
.Replace What:="#N/A", Replacement:="", LookAt:=xlPart, _
です
（砂糖） 2021/05/31(月) 15:56

ご返信ありがとうございます。

下記の通り修正致しましたが
オブジェクトが必要ですというエラーとなってしまいました。

.FillDown
.copy
.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
.Replace What:="#N/A", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2

Selectionにしたところエラーは起きなかったのですが、
正しくコピペされておりませんでした・・・

MyR.Offset(3, 0)を選択セルにしたいのですが、
Selectionでは駄目なのでしょうか？
（ミシェル） 2021/05/31(月) 16:51

ちょっと確認。提示されたマクロをちょっと手直しすると、前半はこんな感じになるとおもいますが、

    Sub 名も無きマクロ()
        Dim MyR As Range
        Dim bufRNG As Range

        For Each MyR In Workbooks("C.xlsm").Worksheets("D").Range("B2:AM2")
            If Workbooks("A.xlsm").Worksheets("B").Range("C4").Value = MyR.Value Then
                If bufRNG Is Nothing Then
                    Set bufRNG = MyR
                Else
                    Set bufRNG = Union(bufRNG, MyR)
                End If
            End If
        Next

        If Not bufRNG Is Nothing Then
            bufRNG.Offset(3).Formula = "=VLOOKUP(B5,'[A.xlsm]B'!$A$4:$C$300,3,0)"
        End If

    End Sub

■１
上記の数式において、B5セルというのは、相対参照であってるんですか？
相対参照であってる場合、B2セルが条件を満たした（AブックのBシートのC4セルの値と一致した）場合すべてが循環参照（数式を書き込むセルと検索値が書かれているセルが同じになってしまう）になりますよね？
さらに、B2セルで条件を満たさなかったら循環参照にはならないでしょうが、今度は、見つかったセルの列とB列の関係性が固定できないため、少なくともB列は絶対参照が正しいのではありませんか？
（その場合、B列から判定対象にすると前述のとおり循環参照になるから"C2:AM2"にすべきではありませんか？

■２
後半の部分になりますが、Selection.FillDown だとすれば↓ということになりますが

 Workbooks("C.xlsm").Worksheets("D").Range("B2:AM2").FillDown

この場合、【１行】しかないですが、FillDownは何のために行っているのですか？
場合によっては、せっかくの数式を消しちゃいませんか？

（もこな２） 2021/05/31(月) 20:58

ご返信ありがとうございます。
説明が不十分で申し訳ございません。

■１
数式のB5セルですが、B列のみ絶対参照で数字部分は相対参照になります。
数式だと$B5に変更すればよろしいのでしょうか？

■２
数式が入ったセル（bufRNG.Offset(3)）よりその数式をB列の最終行までコピペするために、
FillDownを使っておりました。
前のマクロになりますが、下記に変更しても動きませんでした。

MyR.Cells.Offset(3, 0).FillDown

その他活用できる数式ございましたらご教授よろしくお願いいたします。

（ミシェル） 2021/06/01(火) 09:40

コメント返信：

[ 一覧(最新更新順) ]

[[20210531125104]] 『VBAでのOffset使用について』（ミシェル） ページの最後に飛ぶ

[[20210531125104]]　『VBAでのOffset使用について』（ミシェル）　ページの最後に飛ぶ