Syntax Error 1004

[Tip1985]

Hi,

I am trying to create a macro with VBA using the following formula:

Please Login or Register  to view this content.

When I try to run the macro it comes up with a syntax error 1004.

If I reduce the formula to

Please Login or Register  to view this content.

it works fine.

It is as though VBA can only handle up to a certain formula length.

Could anyone help?

Cheers
T

[Kaper]

I could not re-create the same error (pitty you've not posted a dummy sample workbook), but just in case:
use such formula instead, it's much shorter and less prone for errors:

Please Login or Register  to view this content.

so basically, you put yout pairs matchedvalue-returnedvalue separated by comma and between each pair put semicolon. (if it's not working with your version of excel - try reversing: comma between pairs and elements in pair separated with semicolon)

[Tip1985]

Hi Kaper,

Thanks for your response. I tried your formula and it doesn't return anything.

I have attached a dummy workbook.

Sheet 1 has your formula applied in column D.
Sheet 2 has your formula with ; & , swapped.
Sheet 3 has my formula.
Sheet 4 I recorded a macro with my formula and I get a message to debug then the syntax error.

Here is the VBA script - It highlights from ActiveCell to the end of the formula in yellow.

Please Login or Register  to view this content.

I am interested if you can replicate my issue.

Cheers
T

[Kaper]

Hi,

With sample data it's obvious that my formula is wrong. So discard it.

As the problem with the formula to be inserted by the VBA - look at front and end of sub strings you splitted thye formula ther are incomplete

Please Login or Register  to view this content.

so VBA could not insert such wrong formula
Right notation could be for instance:

Please Login or Register  to view this content.

Having sample file available, I can propose such (sample - may be not most effective, but I hope easy to read) code to do this without formulas.

Please Login or Register  to view this content.

[Tip1985]

Hi Kaper,

VBA must put breaks in the formula when recording a macro. I copied your formula into VBA and it works seemlessly.

The code you wrote also works fine. If only I could understand it.

Thanks so much for your assistance.