Software

Ask something
Question Locked

VBA formula issue!!

0 helpful
3 replies

by nulubez

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

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?

3 replies

by tobif

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.

by nulubez

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!

This discussion is closed to new replies.