Sir Babydum GBE
07-06-2012, 04:26 AM
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
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