# Formula in Macro

#### G-Matriix

##### Board Regular
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??

Thanks

### Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

#### QuietRiot

##### Well-known Member
i think its the commas or quotes

chr(34) for quotes

ill test this out in a second

#### QuietRiot

##### Well-known Member
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

#### G-Matriix

##### Board Regular
That worked great thanks Alot!

#### QuietRiot

##### Well-known Member
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

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

#### Norie

##### Well-known Member
The single quotes are caused by using FormulaR1C1 with a formula that contains A1 notation.

#### QuietRiot

##### Well-known Member
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) & "))))"``

#### G-Matriix

##### Board Regular
It is working now.......Thanks A Bunch!!

L

#### Legacy 51064

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

Replies
9
Views
279
Replies
1
Views
951
Replies
2
Views
291
Replies
0
Views
289
Replies
24
Views
383

1,191,054
Messages
5,984,378
Members
439,883
Latest member
onions44

### 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.

### Which adblocker are you using?

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

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