Solved: Call cell by its name - not R1C1

[Sir Babydum GBE]

Its driving me potty and I'm sure tha answer is simple

What I want to do is for Excel to note the cell address one cell above the active cell, then refer to that cell in a formula.

So lets assume I've entered details in Cell B3 and hit ENTER so that the active cell is now B4.

Take a look at this code:


Dim HomeCell As Range
Set HomeCell = ActiveCell.Offset(-1, 0)

Range("E3").Formula = "=IF(LEFT(" & HomeCell & ",1)<>""U""," & HomeCell & ",0)"


What I expect to appear in E3 is:

=IF(LEFT(B3,1)<>"U",B3,0)
But What I actually get in the cell is this:=IF(LEFT(3:3-1-1,1)<>"U",3:3-1-1,0)

Pls help. The universe will be a better place.

BD

[Kenneth Hobs]

Range("E3").Formula = "=IF(LEFT(" & HomeCell.Address(False, False) & ",1)<>""U""," & HomeCell & ",0)"

[nilem]

Range("E3").Formula = "=IF(LEFT(" & HomeCell.Address(0, 0) & ",1)<>""U""," & HomeCell.Address(0, 0) & ",0)"

[Sir Babydum GBE]

Range("E3").Formula = "=IF(LEFT(" & HomeCell.Address(0, 0) & ",1)<>""U""," & HomeCell.Address(0, 0) & ",0)"
Thanks both

First example placed apostrophes around the B4 and returnes a NAME error.

Second worked perfectly.

Thx again

[Kenneth Hobs]

I did not see your second reference. I usually build the string separate and then use Debug.print to be sure that the string is built correctly.

Range("E3").Formula = "=IF(LEFT(" & HomeCell.Address(False, False) & ",1)<>""U""," & HomeCell.Address(False, False) & ",0)"