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

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

Hi JasonK

Double quote signs in formulas created by VBA is always a 'problem', and
change :
Selection.Cells.formula.Offset(, 9)

to:

Selection.Offset(,9).formula=...


Sub aa()
Dim dQuote As String
Dim aCell As String
dQuote = """"
aCell = ActiveCell.Address
myformula = "=MID(" & aCell & ",FIND(" & dQuote & " " & dQuote _
& "," & aCell & ")+1,LEN(" & aCell & ")-FIND(" & dQuote & _
" " & dQuote & "," & aCell & "))&" & dQuote & " " & dQuote _
& "&LEFT(" & aCell & ",FIND(" & dQuote & " " & dQuote & _
"," & aCell & ")-1)"
ActiveCell.Offset(, 9).Formula = myformula

End Sub

Hopes this helps.
....
Per

"JasonK" skrev i meddelelsen
...
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

Jacob Skaria,
That worked perfectly. Thank you again.
And, I think I actually understand why it worked!
JasonK



On Wed, 5 Aug 2009 10:21:02 -0700, Jacob Skaria
wrote:

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

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

First off, I'd like to suggest you use this less complicated formula (half
as many function calls) in place of the one you posted...

=MID(C20&" "&C20,FIND(",",C20)+2,LEN(C20))

Now, as for the macro to implement it...

Sub PlaceNameReversalFormula()
Dim R As Range
For Each R In Selection
R.Offset(, -9).Formula = "=MID(" & R.Address & "&"" ""&" & _
R.Address & ",FIND("",""," & R.Address & _
")+2,LEN(" & R.Address & "))"
Next
End Sub

As structured, the above macro will allow you to select more than one cell
and have all of the cells processed with a single call to the macro.

--
Rick (MVP - Excel)


"JasonK" wrote in message
...
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