VBA formula issue!!

By nulubez ·
I am using the following formula in a Macro to sort names by lastName, firstName middleInitial:

Range("C6").Formula = "= IF(A6<>"",(TRIM((MID(B6,FIND(" ",B6)+1,LEN(B6)-FIND(" ",B6))) & ", " & TRIM(LEFT(B6,FIND(" ",B6))))),"")"

My issue is that when I place this in an excel cell it rearranges the names perfectly but when I place it into VBA I get a "mismatch" error once I run the macro! Any ideas?

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Answers

Collapse -


by TobiF In reply to VBA formula issue!!

It's tricky to inject formulas as text from VBA into Excel:
1. Addresses won't translate with regards to location (unless the formula is converted into relative addresses)
2. You depend on current locale settings.

I'd suggest you record a new macro where you enter/edit this formula. That should at least give you the correct relative format.

Edit: Deleted the last line, which was a leftover from the editing process.

Collapse -

Thank you

by nulubez In reply to Tricky

Thanks for the feed back. Just to keep moving forward with the project while waiting for a response I recorded the coping of the formula into the cells and copied the needed code into the macro I am working with. Like you said - quick fix!

Related Discussions

Related Forums