| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 . |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 . |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| referencing text in an IF statement | Excel Worksheet Functions | |||
| Fixed/Locked cell referencing | Excel Discussion (Misc queries) | |||
| IF Statement: cell referencing | Excel Worksheet Functions | |||
| referencing cells including an IF statement??? | Excel Worksheet Functions | |||
| drawdown at fixed rate over set period from investment at fixed % | New Users to Excel | |||
Linear Mode
