macro / formula problem -- rearranging names in a selected cell

[JasonK]

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]

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

 

[Per Jessen]

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]

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

 

[Lars-Åke Aspelin]

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

 

[Rick Rothstein]

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.