Formula works in excel, but not in VBA

[ELIVERGARA]

I am trying to use a formula in a macro to find only number values greater than 89999.
The list is made of job numbers in the 70-80000's for regular jobs, and 90,000 for jobs that should be capitalized. The problem is that some of the 70 & 80000 job numbers have -1, -2 (e.g. 73500-1, etc). The "dash" job numbers remain stored as text whereas the rest are stored as number.

When I input the following formula in excel, it works perfectly:
=IF(AND(ISNONTEXT(B2),((B2>89999))),"Yes","No")
It tells me "Yes" if the job is greater than 90000, and if not, (wether it is less than 89999 or text) it says "No". This helps me then generate a pivot table that pulls only the 90000 jobs.

BUT... When I enter it on my macro as this:

ActiveCell.FormulaR1C1Local= "=IF(AND(ISNONTEXT(B2),((B2>89999))),"Yes","No")"

I get a "Compiling error, expected end of statement" with the word "Yes" highlighted.

How can I make formula work from a macro?



Thanks in advance!

Eli

[zbor]

You need double quotation.
Try:

Please Login or Register  to view this content.

[ELIVERGARA]

Well.. I added the double quotation but now I get #NAME? in each cell.

[GC Excel]

Hi,

If you want to refer to cell B2, then do not use the R1C1 notation. Your code should be :

Please Login or Register  to view this content.

If you want to use R1C1 notation, then it should be :

Please Login or Register  to view this content.

[GC Excel]

... and let's say you want to copy the formula in column C, then using the R1C1 notation, it would be :

Please Login or Register  to view this content.

(Adapt the range as required)