• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1326
  • Last Modified:

Relative vs Absolute Cell Reference with VBA

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
David Phelops
Asked:
David Phelops
1 Solution
 
MichaelBusiness AnalystCommented:
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
 
David PhelopsAuthor Commented:
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now