formula change problem

C

cparsons

I have a working formula that looks like this:


Code
-------------------

.Offset(P, 5).Formula = "=IF(RC[-1]>100,IF(RC[1]=""Y"", if(rc[2]=""US"",""TRUE"")),""FALSE"")"

-------------------


and I want to change it to:


Code
-------------------

.Offset(P, 5).Formula = "=IF(.offset(P, 4) > 100, " & _
"IF(.offset(P, 6) =""Y"", " & _
"IF(.offset(P, 7) =""US"",""TRUE"")),""FALSE"")"

-------------------


but I keep getting an application error when the macro runs and it i
because of the formula.

Any suggestions?

Thanks
 
F

Frank Kabel

Hi
your mixing the .offset statement with a formula. Why don't you use the
first one?
 
D

Dave Peterson

First, I think I'd use .formulaR1C1 for that first example (not .formula).

Second, you're mixing VBA into your formula (Frank's point) and you can't do
that in the formula:

.Offset(P, 5).Formula = "=IF(.offset(P, 4) > 100, " & _
"IF(.offset(P, 6) =""Y"", " & _
"IF(.offset(P, 7) =""US"",""TRUE"")),""FALSE"")"

could become:

.Offset(P, 5).Formula = "=IF(" & .offset(P, 4).address(external:=true) _
& " > 100, " & _
"IF(" & .offset(P, 6).address(external:=true) & " =""Y"", " & _
"IF(" & .offset(P, 7).address(external:=true) & "=""US"",""TRUE""))" _
& ,""FALSE"")"

===

But it might be easier seen using your .formulaR1C1 formula. I'm not quite sure
what you're doing or how you're finding stuff, but this syntax may work:

.Offset(P, 5).FormulaR1C1 = "=IF(RC[" & Variable1 & "]>100,IF(RC[" _
& variable2 & "]=""Y"",
if(rc[" & variable3 & "]=""US"",""TRUE"")),""FALSE"")"

I just don't know what to use for those variables (1-3). But you can use
expressions that evaluate as numbers:


.Offset(P, 5).FormulaR1C1 = "=IF(RC[" & P+3 & "]>100,IF(RC[" _
& P*5-2 & "]=""Y"",
if(rc[" & P+17 & "]=""US"",""TRUE"")),""FALSE"")"


Maybe you'll see something that makes sense <bg>.

I have a working formula that looks like this:

Code:
--------------------

.Offset(P, 5).Formula = "=IF(RC[-1]>100,IF(RC[1]=""Y"",
if(rc[2]=""US"",""TRUE"")),""FALSE"")"

--------------------

and I want to change it to:

Code:
--------------------

.Offset(P, 5).Formula = "=IF(.offset(P, 4) > 100, " & _
"IF(.offset(P, 6) =""Y"", " & _
"IF(.offset(P, 7) =""US"",""TRUE"")),""FALSE"")"

--------------------

but I keep getting an application error when the macro runs and it is
because of the formula.

Any suggestions?

Thanks,
Click to expand...
 
Top