EXCELでVBAマクロを使ってコピペ→コピー元は削除したい

Question

EXCELでVBAマクロを使ってコピペ→コピー元は削除したい

お世話になります。

下記の処理を実行した後、その値だけL列にペーストする事は可能でしょうか。

そして関数が表示されているK列の数値を削除したいです。

目的としては、計算結果の部分に関数を表示させない為です。

ActiveSheet.Rangeというのでやってみましたが、上手く行かず、途方にくれておりお手数ですがご教示をお願いできないでしょうか。

よろしくお願い致します。

Sub test()
With Worksheets("Sheet1")
With .Range("K10", .Cells(Rows.Count, "J").End(xlUp).Offset(, 1))
.NumberFormatLocal = "#,##0_ ;[赤]-#,##0"
.Formula = "=ROUNDDOWN(J10*(100*VLOOKUP(Sheet2!$I$7,'Sheet3'!$A$2:$B$" & Rows.Count & ",2,FALSE))/100,0)"
End With
End With
MsgBox "処理が終了しました"
End Sub

Comments

[meg_]

コードが見にくいので、「コードの挿入」にて修正お願いします。

[meg_]

イメージを掴むため、サンプルのシート画像を追加してもらえませんか？

[ko1]

わかりにくく申し訳ありません。追加いたしました。ご教示の程よろしくお願い申し上げます。

Answer 1

下記のように2行追加すればいいでしょう。（見やすいようにインデントを追加してます。）

Sub test()
    With Worksheets("Sheet1")
        With .Range("K10", .Cells(Rows.Count, "J").End(xlUp).Offset(, 1))
            .Offset(, 1).NumberFormatLocal = "#,##0_ ;[赤]-#,##0" '次の列に書式設定
            .Formula = "=ROUNDDOWN(J10*(100*VLOOKUP(Sheet2!$I$7,'Sheet3'!$A$2:$B$" & Rows.Count & ",2,FALSE))/100,0)"
            .Offset(, 1).Value = .Value '次の列(L列）に値をコピー
            .ClearContents              '数式削除
        End With
    End With
    MsgBox "処理が終了しました"
End Sub

計算列の数式を削除してしまうのなら、計算列はなくてもいいですね。計算列は削除して、K列を支払金額にして、K列を値で上書きすればOKです。

Sub test()
    With Worksheets("Sheet1")
        With .Range("K10", .Cells(Rows.Count, "J").End(xlUp).Offset(, 1))
            .NumberFormatLocal = "#,##0_ ;[赤]-#,##0" '書式設定
            .Formula = "=ROUNDDOWN(J10*(100*VLOOKUP(Sheet2!$I$7,'Sheet3'!$A$2:$B$" & Rows.Count & ",2,FALSE))/100,0)"
            .Value = .Value '値で上書き
        End With
    End With
    MsgBox "処理が終了しました"
End Sub

Comments

[ko1]

やりたかった事、そして思っていたけどできなかった事を実現して頂きました。感謝申し上げます。ありがとうございます！

Answer 2

コピー元は削除したい

んと、他のセルを参照している計算式を消して、
値のみ残したいということですよね？

Range("A1").Formula = "=D1"
range("A1").Value = Range("A1").Value
Msgbox Range("A1").Formula

というようにしてみると、
計算式の引き出に数式を入れたあと、
値の引き出しから値を取り出して、
再度値の引き出しに値を入れると、
数式の引き出しには、最初にいれた数式が消えて、
値と同じものが確認出来ると思います。

なので、
With 該当セル範囲
.Value = .Value
End With
とやって数式を消すことが常套手段となっています。

コピーして、
同じセル範囲に値のみ貼り付けとしても、
同じ結果になります。
処理速度に差があるようなので、
気になるなら、両方試してみるのも悪くないです。
（情報量により速度の有利不利があるようです。）

Answer 3

コピー＆ペースト機能に拘らなければ、こういう方法もあります。

Sub test()
With Worksheets("Sheet1")
With .Range("K10", .Cells(Rows.Count, "J").End(xlUp).Offset(, 1))
- .NumberFormatLocal = "#,##0_ ;[赤]-#,##0"
.Formula = "=ROUNDDOWN(J10*(100*VLOOKUP(Sheet2!$I$7,'Sheet3'!$A$2:$B$" & Rows.Count & ",2,FALSE))/100,0)"
+ .Offset(, 1).Value = .Value
+ .Offset(, 1).NumberFormatLocal = "#,##0_ ;[赤]-#,##0"
+ .Value = ""
End With
End With
MsgBox "処理が終了しました"
End Sub

Answer 4

K列の対象範囲をコピー、L列へ値の貼り付けで良いと思います。その後、K列の対象範囲をクリアします。（具体的なコードは、マクロの記録で実際に生成されたコードを参考にすると良いですよ）

Answer 5

コピーしたいデータの初期位置から
末端までデータをコピーして
Cells(, ).End(xlUp).Row 構文はググって下さい
削除したいデータのK列の末端まで空白を埋めるだけでは？