Solved

Relative vs Absolute Cell Reference with VBA

Posted on 2013-10-24
2
989 Views
Last Modified: 2013-10-24
Dear Experts

Simple Line of Code to enter a formula in the active cell using VBA

Sub RelativeCellReference()
    
    ActiveCell = "=Trim(RC" & 1 & ")"

End Sub

Open in new window


I want this formula to mean Active cell = value of cell in active row, column 1
The formula that is being returned is relative both to column and row.

If i type the formula directly into the active cell. I.e Trim(RC1) it returns the correct answer.

What in the name of all that is VBA am I doing wrong.  Wood, trees etc....
Help

Thank you
David
RelativeCellReference.xlsm
0
Comment
Question by:David Phelops
2 Comments
 
LVL 6

Accepted Solution

by:
Michael earned 500 total points
ID: 39597226
Hello David,

Try this:

Sub RelativeCellReference()
    
    ActiveCell.FormulaR1C1 = "=Trim(RC" & 1 & ")"

End Sub

Open in new window


Without the .FormulaR1C1, Excel thinks you want to Trim column RC row 1.

JazzyJoop
0
 

Author Closing Comment

by:David Phelops
ID: 39597254
Works for me too.  :-) phew.

Makes sense now.  First time that actually I see the practical value of "FormulaR1C1"

Many thanks. Most appreciated
David
0

Featured Post

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

786 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