Referencing a fixed cell in VBA using an IF statement
 

Dear all,

I'd be grateful for some advice on how to reference a
fixed cell in a formula that I wish to copy down a row of
cells.

I'm writing some code to put a formula into a cell using
an If statement. I want to pick up a value from a cell
that is anchored ($S$1) but I can't seem to get it
correct in the formula. I've also tried setting it up as
a variable but that doesn't work either. The code below
works except for the "=$S$1" part.

ActiveCell.FormulaR1C1 = "=IF((RC[-4]= ""h,"=$s$1",""K"")"

Any guidance would be gratefully received!

thanks,

Neil

Referencing a fixed cell in VBA using an IF statement
 

Hi Neill,

I think this is what you want

ActiveCell.FormulaR1C1 = "=IF(R1C3=""h"",R1C19,""K"")"


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Neil Crawford" wrote in message
...
Dear all,

I'd be grateful for some advice on how to reference a
fixed cell in a formula that I wish to copy down a row of
cells.

I'm writing some code to put a formula into a cell using
an If statement. I want to pick up a value from a cell
that is anchored ($S$1) but I can't seem to get it
correct in the formula. I've also tried setting it up as
a variable but that doesn't work either. The code below
works except for the "=$S$1" part.

ActiveCell.FormulaR1C1 = "=IF((RC[-4]= ""h,"=$s$1",""K"")"

Any guidance would be gratefully received!

thanks,

Neil

Referencing a fixed cell in VBA using an IF statement
 

ActiveCell.FormulaR1C1 = "=IF(RC[-4]= ""h"",R1C19,""K"")"

Neil Crawford wrote:
Dear all,

I'd be grateful for some advice on how to reference a
fixed cell in a formula that I wish to copy down a row of
cells.

I'm writing some code to put a formula into a cell using
an If statement. I want to pick up a value from a cell
that is anchored ($S$1) but I can't seem to get it
correct in the formula. I've also tried setting it up as
a variable but that doesn't work either. The code below
works except for the "=$S$1" part.

ActiveCell.FormulaR1C1 = "=IF((RC[-4]= ""h,"=$s$1",""K"")"

Any guidance would be gratefully received!

thanks,

Neil

Referencing a fixed cell in VBA using an IF statement
 

ActiveCell.FormulaR1C1 = "=IF(RC[-4]= ""h"",$s$1,""K"")"

Check it from the immediate window:

? "=IF(RC[-4]=""h"",$s$1,""K"")"
=IF(RC[-4]="h",$s$1,"K")

--
Regards,
Tom Ogilvy


"Neil Crawford" wrote in message
...
Dear all,

I'd be grateful for some advice on how to reference a
fixed cell in a formula that I wish to copy down a row of
cells.

I'm writing some code to put a formula into a cell using
an If statement. I want to pick up a value from a cell
that is anchored ($S$1) but I can't seem to get it
correct in the formula. I've also tried setting it up as
a variable but that doesn't work either. The code below
works except for the "=$S$1" part.

ActiveCell.FormulaR1C1 = "=IF((RC[-4]= ""h,"=$s$1",""K"")"

Any guidance would be gratefully received!

thanks,

Neil

Referencing a fixed cell in VBA using an IF statement
 

Whoops, mixed reference types:

ActiveCell.FormulaR1C1 = "=IF(RC[-4]= ""h"",R1C19,""K"")"

--
Regards,
Tom Ogilvy

"Tom Ogilvy" wrote in message
...
ActiveCell.FormulaR1C1 = "=IF(RC[-4]= ""h"",$s$1,""K"")"

Check it from the immediate window:

? "=IF(RC[-4]=""h"",$s$1,""K"")"
=IF(RC[-4]="h",$s$1,"K")

--
Regards,
Tom Ogilvy


"Neil Crawford" wrote in message
...
Dear all,

I'd be grateful for some advice on how to reference a
fixed cell in a formula that I wish to copy down a row of
cells.

I'm writing some code to put a formula into a cell using
an If statement. I want to pick up a value from a cell
that is anchored ($S$1) but I can't seem to get it
correct in the formula. I've also tried setting it up as
a variable but that doesn't work either. The code below
works except for the "=$S$1" part.

ActiveCell.FormulaR1C1 = "=IF((RC[-4]= ""h,"=$s$1",""K"")"

Any guidance would be gratefully received!

thanks,

Neil

Referencing a fixed cell in VBA using an IF statement
 

Sorry for some reason I read the if as $C$1, should be

ActiveCell.FormulaR1C1 = "=IF(RC[-4]=""h"",R1C19,""K"")"


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Bob Phillips" wrote in message
...
Hi Neill,

I think this is what you want

ActiveCell.FormulaR1C1 = "=IF(R1C3=""h"",R1C19,""K"")"


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Neil Crawford" wrote in message
...
Dear all,

I'd be grateful for some advice on how to reference a
fixed cell in a formula that I wish to copy down a row of
cells.

I'm writing some code to put a formula into a cell using
an If statement. I want to pick up a value from a cell
that is anchored ($S$1) but I can't seem to get it
correct in the formula. I've also tried setting it up as
a variable but that doesn't work either. The code below
works except for the "=$S$1" part.

ActiveCell.FormulaR1C1 = "=IF((RC[-4]= ""h,"=$s$1",""K"")"

Any guidance would be gratefully received!

thanks,

Neil

Referencing a fixed cell in VBA using an IF statement
 

Tom,

thanks, will give it a go,

Neil
-----Original Message-----
Whoops, mixed reference types:

ActiveCell.FormulaR1C1 = "=IF(RC[-4]= ""h"",R1C19,""K"")"

--
Regards,
Tom Ogilvy

"Tom Ogilvy" wrote in message
...
ActiveCell.FormulaR1C1 = "=IF(RC[-4]
= ""h"",$s$1,""K"")"

Check it from the immediate window:

? "=IF(RC[-4]=""h"",$s$1,""K"")"
=IF(RC[-4]="h",$s$1,"K")

--
Regards,
Tom Ogilvy


"Neil Crawford"
wrote in message
...
Dear all,

I'd be grateful for some advice on how to reference a
fixed cell in a formula that I wish to copy down a
row of
cells.

I'm writing some code to put a formula into a cell
using
an If statement. I want to pick up a value from a
cell
that is anchored ($S$1) but I can't seem to get it
correct in the formula. I've also tried setting it
up as
a variable but that doesn't work either. The code
below
works except for the "=$S$1" part.

ActiveCell.FormulaR1C1 = "=IF((RC[-4]
= ""h,"=$s$1",""K"")"

Any guidance would be gratefully received!

thanks,

Neil




.

Referencing a fixed cell in VBA using an IF statement
 

Bob,

thanks, will give it a go,

Neil
-----Original Message-----
Sorry for some reason I read the if as $C$1, should be

ActiveCell.FormulaR1C1 = "=IF(RC[-4]=""h"",R1C19,""K"")"


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Bob Phillips" wrote
in message
...
Hi Neill,

I think this is what you want

ActiveCell.FormulaR1C1 = "=IF(R1C3=""h"",R1C19,""K"")"


--

HTH

Bob Phillips
... looking out across Poole Harbour to the
Purbecks
(remove nothere from the email address if mailing
direct)

"Neil Crawford"
wrote in message
...
Dear all,

I'd be grateful for some advice on how to reference a
fixed cell in a formula that I wish to copy down a
row of
cells.

I'm writing some code to put a formula into a cell
using
an If statement. I want to pick up a value from a
cell
that is anchored ($S$1) but I can't seem to get it
correct in the formula. I've also tried setting it
up as
a variable but that doesn't work either. The code
below
works except for the "=$S$1" part.

ActiveCell.FormulaR1C1 = "=IF((RC[-4]
= ""h,"=$s$1",""K"")"

Any guidance would be gratefully received!

thanks,

Neil




.

Referencing a fixed cell in VBA using an IF statement
 

J,

thanks,

Neil
-----Original Message-----
ActiveCell.FormulaR1C1 = "=IF(RC[-4]= ""h"",R1C19,""K"")"

Neil Crawford wrote:
Dear all,

I'd be grateful for some advice on how to reference a
fixed cell in a formula that I wish to copy down a row
of
cells.

I'm writing some code to put a formula into a cell
using
an If statement. I want to pick up a value from a cell
that is anchored ($S$1) but I can't seem to get it
correct in the formula. I've also tried setting it up
as
a variable but that doesn't work either. The code
below
works except for the "=$S$1" part.

ActiveCell.FormulaR1C1 = "=IF((RC[-4]
= ""h,"=$s$1",""K"")"

Any guidance would be gratefully received!

thanks,

Neil
.