asked on

R1C1 Issue

Hello All,
I have a R1C1 issue. To keep it simple, I only have three column. What I need:
Columns C1 and C2 needs to be added up and then the result needs to be pasted as values under the column “Sum”

I did it by pasting the result somewhere as values and then copying over the results to the “Sum” column- Can somebody indicate if I am doing anything wrong – or if it needs to be improved or more efficient? I may be doing some extra steps - I believe the awesome EE experts will definitely have a better suggestion :)
I am dealing with 35000 rows, so figured that the R1C1 would be handy for that situation.
R1C1-issue.xlsm

CompProbSolv

In the cell immediately below the cell with the word "Sum" in it, enter the following:
=RC[-2]+RC[-1]
Copy that cell and paste it to the entire column below it, as far as your data goes.

ASKER CERTIFIED SOLUTION

Steve

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Rayne

ASKER

Perfect The Barman,

This is exactly what I wanted. Can't thank you enough for this awesome suggestion. I wanted first the values to be calculated and then pasted as values (instead of the calculations)

Please let me know if there is any potential issues (if any) of this or this is totally fine. I would be using this copy & paste as values long term

Thank you

Steve

There is another method, which can be more robust, but slower over larger data sets:
But this just plops the value in, so no formatting.

Sub CreateFormula()
    Dim CopyRange As Range, c as long
    Set CopyRange = Sheets("Sheet1").Range("D3:D6")
    For Each c In CopyRange
        c.Value = Cells(c.Row, c.Column - 2) + Cells(c.Row, c.Column - 1)
    Next
End Sub

Open in new window

Hamed Nasr

You can change your range to reflect the required range.

Sub Button1_Click()
Call CreateFormula
End Sub

Sub CreateFormula()
Dim CopyRange As Range, m, k

'Set CopyRange = Sheets("Sheet1").Range("D3:D6") D6 is changed to any relevant number
Set CopyRange = Sheets("Sheet1").Range("D3:D14")
CopyRange.FormulaR1C1 = "=RC[-1]+RC[-2]"
CopyRange.Copy
Sheet1.Range("F3").PasteSpecial xlPasteValuesAndNumberFormats

Selection.Copy
CopyRange.PasteSpecial xlPasteValuesAndNumberFormats

Application.CutCopyMode = False
End Sub
R1C1-issue-2.xlsm

Rayne

ASKER

Hello All,

Thanks for the suggestion.

The Barman - The For each C loop does takes a heck of a long time. So I will stick with the code you gave earlier - its lighting fast for sure, considering 35000 rows :)