On Wed, 05 Aug 2009 09:58:10 -0700, JasonK wrote:

Thank you all in advance once again. This group has been incredible.

I need to run a macro that takes a name (formatted LastName,
FirstName) in a selected cell, and places the name FirstName LastName,
(I need the comma at the end) 9 cells over from the selected cell.

So far, I have this formula that works when I paste the formula in the
cell that I want the result to end up in.

=MID(C20,FIND(" ",C20)+1,LEN(C20)-FIND(" ",C20))&" "&LEFT(C20,FIND("
",C20)-1)

It doesn't work when I try to paste this formula using a macro with
syntax like:

Selection.Cells.formula.Offset(, 9) = " =MID(C20,FIND("
",C20)+1,LEN(C20)-FIND(" ",C20))&" "&LEFT(C20,FIND(" ",C20)-1)"

I don't always want to reference cell C20 either. I need to reference
the cell that is selected before the macro runs.

Excel wont even let me make the line in the macro giving me a "compile
error".

I know formulas and macros are different animals, but I have pasted
other formulas in other macros and they have worked as long as the
quotation marks were proper.

Again thanks in advance for your help.

JasonK


Try this macro:

Sub test()
With Selection
.Offset(, 9) = Right(.Cells, Len(.Cells) - InStr(.Cells, ",") -
1) & " " & Left(.Cells, InStr(.Cells, ","))
End With
End Sub

Hope this helps / Lars-Åke