Formula in Macro

[G-Matriix]

Hello All,

I am trying to have my macro put in a formula and I am getting a error. The formula I was trying to put in was this:

=IF(AND(OR(X2>0,Y2>0),(AND(AE2<=0,AF2<=0))),"Late PU",IF(AND(AND(X2<=0,Y2<=0),(OR(AE2>0,AF2>0))),"Late Del",IF(AND(OR(X2>0,Y2>0),(OR(AE2>0,AF2>0))),"Both",IF(AND(X2<=0,Y2<=0,AE2<=0,AF2<=0,AH2<>""),"LRC","NONE"))))

I have it in VB as:

Range("BV2").Select
ActiveCell.FormulaR1C1 = "=IF(AND(OR(RC[-52]>0,RC[-51]>0),(AND(RC[-45]<=0,RC[-44]<=0))),"Late PU",IF(AND(AND(RC[-52]<=0,RC[-51]<=0),(OR(RC[-45]>0,RC[-44]>0))),"Late Del",IF(AND(OR(RC[-52]>0,RC[-51]>0),(OR(RC[-45]>0,RC[-44]>0))),"Both",IF(AND(RC[-52]<=0,RC[-51]<=0,RC[-45]<=0,RC[-44]<=0,RC[-42]<>""),"LRC","NONE"))))"
Selection.AutoFill Destination:=Range("BV2:BV5000"), Type:=xlFillDefault

However, It is giving me a error. I imagine that the formula is too long but I am not sure. Any Ideas??

Please help!

Thanks

 

[QuietRiot]

i think its the commas or quotes

chr(34) for quotes

ill test this out in a second

 

[QuietRiot]

try this:

"=IF(AND(OR(X2>0,Y2>0),(AND(AE2<=0,AF2<=0)))," & Chr(34) & "Late PU" & Chr(34) & ",IF(AND(AND(X2<=0,Y2<=0),(OR(AE2>0,AF2>0)))," & Chr(34) & "Late Del" & Chr(34) & ",IF(AND(OR(X2>0,Y2>0),(OR(AE2>0,AF2>0)))," & Chr(34) & "Both" & Chr(34) & ",IF(AND(X2<=0,Y2<=0,AE2<=0,AF2<=0,AH2<>" & Chr(34) & Chr(34) & ")," & Chr(34) & "LRC" & Chr(34) & "," & Chr(34) & "NONE" & Chr(34) & "))))"

PS.. that was brutal sticking all those chr(34)'s in there

 

[G-Matriix]

That worked great thanks Alot!

 

[QuietRiot]

are you sure its working?

for some reason im getting single quotes around x2, y2 etc. if you take them out it seems to be working.

not sure why the single quotes came up.

 

[G-Matriix]

your right............maybe there is another way...I would be able to take the single quote out of all of these....

 

[Norie]

The single quotes are caused by using FormulaR1C1 with a formula that contains A1 notation.

 

[QuietRiot]

The single quotes are caused by using FormulaR1C1 notation with a formula that contains A1 notation.

oh, lol

so just change it to formula instead

Range("BV2").Formula = "=IF(AND(OR(X2>0,Y2>0),(AND(AE2<=0,AF2<=0)))," & Chr(34) & "Late PU" & Chr(34) & ",IF(AND(AND(X2<=0,Y2<=0),(OR(AE2>0,AF2>0)))," & Chr(34) & "Late Del" & Chr(34) & ",IF(AND(OR(X2>0,Y2>0),(OR(AE2>0,AF2>0)))," & Chr(34) & "Both" & Chr(34) & ",IF(AND(X2<=0,Y2<=0,AE2<=0,AF2<=0,AH2<>" & Chr(34) & Chr(34) & ")," & Chr(34) & "LRC" & Chr(34) & "," & Chr(34) & "NONE" & Chr(34) & "))))"

 

[G-Matriix]

It is working now.......Thanks A Bunch!!

 

[Legacy 51064]

How about a find " and replace with "" in the VBA editor?