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
RayneAsked:
Who is Participating?
 
SteveConnect With a Mentor Commented:
Not sure if this is much better, but it may be:
Sub CreateFormula()
    Dim CopyRange As Range, m, k
    Set CopyRange = Sheets("Sheet1").Range("D3:D6")
    CopyRange.FormulaR1C1 = "=RC[-1]+RC[-2]"
    CopyRange.Copy
    CopyRange.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
End Sub

Open in new window

0
 
CompProbSolvCommented:
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.
0
 
RayneAuthor Commented:
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
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
SteveCommented:
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

0
 
hnasrCommented:
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
0
 
RayneAuthor Commented:
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 :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.