CODEWITHSUNDEEP
excelvba
Marc225
Marc225

Reputation: 91

How to subtract two dynamic ranges and paste it to another cell

I have made a macro that copies two dynamic table columns from one worksheet to another. On the Second worksheet I want to subtract those two columns and paste the result on a separate column/vector. All of this needs to be dynamic since I plan on running the macro once a day.

The closest I have come is the following code: 

Sub Makro2()

Dim ws_3 As Worksheet
Set ws_3 = ThisWorkbook.Worksheets(2)


Application.CutCopyMode = False
ws_3.Range("E3:E400").FormulaR1C1 = "=RC[-2]-RC[-1]"

End Sub

So all I need in reality is for E3:E400 to be dynamic since the range of the other two columns change every day.

PS. Rather new at VBA.

Upvotes: 1

Views: 619

Answers (2)

Error 1004
Error 1004

Reputation: 8230

You could try:

Option Explicit

Sub test()

    Dim wsSource As Worksheet, wsDestination As Worksheet
    Dim LastRow1 As Long, LastRow2 As Long, rng1  As Range, rng2 As Range, LastColumn As Long

    With ThisWorkbook
        Set wsSource = .Worksheets("Sheet1") '<- Data appears here
        Set wsDestination = .Worksheets("Sheet2") '<- Data will be copy here
    End With

    With wsSource
        'Let's say the two columns we want to copy is column A & B. Find Last row of A & B
        LastRow1 = .Cells(.Rows.Count, "A").End(xlUp).Row
        LastRow2 = .Cells(.Rows.Count, "B").End(xlUp).Row

        'Create the ranges you want to copy
        Set rng1 = .Range("A1:A" & LastRow1)
        Set rng2 = .Range("B1:B" & LastRow2)
    End With

    With wsDestination
        'Paste column after the last column of row 1. Find last column row 1
        LastColumn = .Cells(1, .Columns.Count).End(xlToLeft).Column

        rng1.Copy
        .Cells(1, LastColumn + 1).PasteSpecial xlPasteValues
        rng2.Copy
        .Cells(1, LastColumn + 2).PasteSpecial xlPasteValues

    End With

    Application.CutCopyMode = False

End Sub

Upvotes: 0

GMalc
GMalc

Reputation: 2628

This is just basic, ensure you declare your variable. 

Dim lRow As Long
lRow = Range("D" & Rows.Count).End(xlUp).Row
Range("E3:E" & lRow).FormulaR1C1 =

Upvotes: 1

Related Questions