Formula doesn't work when run as code in a macro

• I spent a while creating a formula to format some names in column A the way I want them, and it worked fine. The formula is:

=IF(ISERROR(UPPER(LEFT(RIGHT(A1,LEN(A1)-(FIND(", ",A1)+1)),1)) & " " & PROPER(LEFT(A1,(FIND(", ",A1)-1)))),MID(A1,IF(ISERROR(FIND(",",A1)),FIND(" ",A1),0)+1,1) & " " & LEFT(A1,IF(ISERROR(FIND(",",A1)),FIND(" ",A1),0)-1),UPPER(LEFT(RIGHT(A1,LEN(A1)-(FIND(", ",A1)+1)),1)) & " " & PROPER(LEFT(A1,(FIND(", ",A1)-1))))

When I enter it, then fill/copy it down my column, it works a treat.
However I then recorded a macro of me doing it, and when I look at the code afterwards, it's changed to:

Code
``"=IF(ISERROR(UPPER(LEFT(RIGHT(RC,LEN(RC)-(FIND("", "",RC)+1)),1)) & "" "" & PROPER(LEFT(RC,(FIND("", "",RC)-1)))),MID(RC,IF(ISERROR(FIND("","",RC)),FIND("" "",RC),0)+1,1) & "" "" & LEFT(RC,IF(ISERROR(FIND("","",RC)),FIND("" "",RC),0)-1),UPPER(LEFT(RIGHT(RC,LEN(RC)-(FIND("", "",RC)+1)),1)) & "" "" & PROPER(LEFT(RC,(FIND("", "",RC)-1))))"``

and when I run said macro, the whole column has #VALUE errors.
Any ideas? Thanks!

• Re: Formula doesn't work when run as code in a macro

Can you show the whole code?

• Re: Formula doesn't work when run as code in a macro

Your formula is using RC as a reference, which refers to the cell containing the formula, hence the errors.

Rory
Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

• Re: Formula doesn't work when run as code in a macro

Quote from S O;757403

Can you show the whole code?

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!