Hi Jason

Build the formula in a string variable . Try the below and feedback

Dim lngRow As Long, strFormula As String

lngRow = Selection.Row
strFormula = "=MID(C" & lngRow & ",FIND(CHAR(32),C" & lngRow & _
")+1,LEN(C" & lngRow & ")-FIND(CHAR(32),C" & lngRow & "))& "" "" &" & _
"LEFT(C" & lngRow & ",FIND(CHAR(32),C" & lngRow & ")-1)"
Selection.Cells.Offset(, 9).Formula = strFormula

If this post helps click Yes
---------------
Jacob Skaria


"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