macro / formula problem -- rearranging names in a selected cell
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
|