excel function in VBA?

[spinkung]

Hi again all,

Im trying to use some excel functions in my VBA code but i keep getting an error pop up.

here's what i've got....

Please Login or Register  to view this content.

the error is (highlighted red): expected end of statment.

It's something to do with the 2 speech marks together but i dont know how to get round it.

anyone help??

Cheers,
Spinkung.

[dominicb]

Good evening Spinkung

Your whole formula is encased in quotes. That is quite correct. You also use quotes in your formula, and Excel - being a bit of a simpleton - thinks you are giving it several strings in succession. You need to replace all the " with Chr(34) eg

"=IF(A2<>" & Chr(34) & Chr(34) & ",LEFT((H3... etc

HTH

DominicB

[Carim]

Hi again,

In order to have quotes within a VBA formula, you need to double them, i.e.
"" "" ... 4 times ...

[spinkung]

thanks for your replies but i'm still getting errors with my code.

i've tried....

Please Login or Register  to view this content.

...and i get : runtime error '1004':application-defined or object-defined error

i've tried....

Please Login or Register  to view this content.

...and i get : runtime error '1004':application-defined or object-defined error

can anyone see the error of my ways????

Cheers,
Spin.

[dominicb]

Hi spinkung

Phew!!!

Try :

Please Login or Register  to view this content.

HTH

DominicB

[spinkung]

i know, its a bit of a stretch but it drastically reduces the file size.

that work a treat, cheers dom.

Spinkung.