?
Solved

R1C1 Issue

Posted on 2012-08-16
6
Medium Priority
?
388 Views
Last Modified: 2012-08-16
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
0
Comment
Question by:Rayne
6 Comments
 
LVL 22

Expert Comment

by:CompProbSolv
ID: 38299565
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
 
LVL 24

Accepted Solution

by:
Steve earned 2000 total points
ID: 38299605
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
 

Author Comment

by:Rayne
ID: 38299650
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
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
LVL 24

Expert Comment

by:Steve
ID: 38299657
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
 
LVL 31

Expert Comment

by:hnasr
ID: 38299665
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
 

Author Comment

by:Rayne
ID: 38299806
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

Featured Post

Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wonder what it's like to get hit by ransomware? "Tom" gives you all the dirty details first-hand – and conveys the hard lessons his company learned in the aftermath.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question