Formula in Macro

G-Matriix

Board Regular
Joined
Sep 20, 2006
Messages
139
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:
Code:
=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:
Code:
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
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
i think its the commas or quotes

chr(34) for quotes

ill test this out in a second
 
Upvote 0
try this:

Code:
"=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
 
Upvote 0
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.
 
Upvote 0
your right............maybe there is another way...I would be able to take the single quote out of all of these....
 
Upvote 0
The single quotes are caused by using FormulaR1C1 with a formula that contains A1 notation.
 
Upvote 0
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

Code:
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) & "))))"
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,316
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top