When you have a literal string that contains quotation marks, you need to double them:
Code:.FormulaR1C1 = "=LEFT(C1,FIND("" "",C1)-1)"
Hey all,
When I currently try to run the below macro, I get a syntax error. Basically it performs excel formulas to split names:
I replaced what it initially generated with the below formulas because whatever it was generating when I recorded the macro and executed below formulas, later on it would not work with other spreadsheets, so I copied the actual formulas themselves into the macro but now I get the error:Code:Sub nameSplitter_Improved() ' ' nameSplitter_Improved Macro ' ' Windows("summary_judgment.xlsx").Activate Range("C1").Select ActiveCell.FormulaR1C1 = "=LEFT(C1,FIND(" ",C1)-1)" Range("D1").Select ActiveCell.FormulaR1C1 = _ "=IF(ISERR(MID(C1,FIND(" ",C1)+1,IF(ISERR(FIND(" ",C1,FIND(" ",C1)+1)), FIND(" ",C1),FIND(" ",C1,FIND(" ",C1)+1))-FIND(" ",C1)-1)),"",MID(C1,FIND(" ",C1)+ 1,IF(ISERR(FIND(" ",C1,FIND(" ",C1)+1)),FIND(" ",C1),FIND(" ",C1,FIND(" ",C1)+1))-FIND(" ",C1)-1))" Range("E1").Select ActiveCell.FormulaR1C1 = _ "=RIGHT(C1,LEN(C1)-FIND("*",SUBSTITUTE(C1," ","*",LEN(C1)-LEN(SUBSTITUTE(C1," ","")))))" Range("C1").Select Selection.AutoFill Destination:=Range("C1:C227") Range("C1:C227").Select Range("D1").Select Selection.AutoFill Destination:=Range("D1:D227") Range("D1:D227").Select Range("E1").Select Selection.AutoFill Destination:=Range("E1:E227") Range("E1:E227").Select Columns("C:E").Select Columns("C:E").Copy Columns("F:H").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode = False Columns("C:E").Select Selection.Delete Shift:=xlToLeft Columns("B:B").Select Selection.Delete Shift:=xlToLeft Rows("1:1").Select Range("B1").Activate Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove Range("B1").Select ActiveCell.FormulaR1C1 = "first_name" Range("C1").Select ActiveCell.FormulaR1C1 = "middle_initial" Range("D1").Select ActiveCell.FormulaR1C1 = "last_name" Range("E1").Select End Sub
Code:=LEFT(C1,FIND(" ",C1)-1)Code:=IF(ISERR(MID(C1,FIND(" ",C1)+1,IF(ISERR(FIND(" ",C1,FIND(" ",C1)+1)), FIND(" ",C1),FIND(" ",C1,FIND(" ",C1)+1))-FIND(" ",C1)-1)),"",MID(C1,FIND(" ",C1)+ 1,IF(ISERR(FIND(" ",C1,FIND(" ",C1)+1)),FIND(" ",C1),FIND(" ",C1,FIND(" ",C1)+1))-FIND(" ",C1)-1))Thanks for response.Code:=RIGHT(C1,LEN(C1)-FIND("*",SUBSTITUTE(C1," ","*",LEN(C1)-LEN(SUBSTITUTE(C1," ","")))))
I suspect you might find some further problems because you are using
.FormulaR1C1 but your formula is not in R1C1 format
You may in fact wantbut you also appear to be trying to put than in cell C1 which would cause a circular reference.Code:.Formula = "=LEFT(C1,FIND("" "",C1)-1)"
Hope this helps, good luck.
Peter
Excel 365 - Windows 10
- Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
- If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker
- Read: Forum Rules, Forum Use Guidelines, & FAQ
Completely missed that!
I changed it to this:
And ran the macro with my cursor in B1 cell. And it generates VALUE! errors for cells that are empty in column a. it appears it loops through a1 more than it should. Is there a way to force it only to loop the length of a1 only?Code:Sub nameSplitter_Improved() ' ' nameSplitter_Improved Macro ' ' Windows("summary_judgment.xlsx").Activate Range("C1").Select ActiveCell.Formula = "=LEFT(A1,FIND("" "",A1)-1)" Range("D1").Select ActiveCell.Formula = _ "=IF(ISERR(MID(A1,FIND("" "",A1)+1,IF(ISERR(FIND("" "",A1,FIND("" "",A1)+1)), FIND("" "",A1),FIND("" "",A1,FIND("" "",A1)+1))-FIND("" "",A1)-1)),"""",MID(A1,FIND("" "",A1)+ 1,IF(ISERR(FIND("" "",A1,FIND("" "",A1)+1)),FIND("" "",A1),FIND("" "",A1,FIND("" "",A1)+1))-FIND("" "",A1)-1))" Range("E1").Select ActiveCell.Formula = _ "=RIGHT(A1,LEN(A1)-FIND(""*"",SUBSTITUTE(A1,"" "",""*"",LEN(A1)-LEN(SUBSTITUTE(A1,"" "","""")))))" Range("C1").Select Selection.AutoFill Destination:=Range("C1:C227") Range("C1:C227").Select Range("D1").Select Selection.AutoFill Destination:=Range("D1:D227") Range("D1:D227").Select Range("E1").Select Selection.AutoFill Destination:=Range("E1:E227") Range("E1:E227").Select Columns("C:E").Select Columns("C:E").Copy Columns("F:H").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode = False Columns("C:E").Select Selection.Delete Shift:=xlToLeft Columns("B:B").Select Selection.Delete Shift:=xlToLeft Rows("1:1").Select Range("B1").Activate Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove Range("B1").Select ActiveCell.FormulaR1C1 = "first_name" Range("C1").Select ActiveCell.FormulaR1C1 = "middle_initial" Range("D1").Select ActiveCell.FormulaR1C1 = "last_name" Range("E1").Select End Sub
Thanks for response.
Last edited by johnmerlino; Nov 4th, 2010 at 10:03 PM.
Maybe this?
Code:Sub nameSplitter_Improved() Workbooks("summary_judgment.xlsx").Activate Range("C1:C227").Formula = "=IF(ISNUMBER(FIND("" "", A1)), LEFT(A1,FIND("" "",A1)-1), A1)" Range("D1:D127").Formula = _ "=IF(ISERR(MID(A1,FIND("" "",A1)+1,IF(ISERR(FIND("" "",A1,FIND("" "",A1)+1)), FIND("" "",A1),FIND("" "",A1,FIND("" "",A1)+1))-FIND("" "",A1)-1)),"""",MID(A1,FIND("" "",A1)+ 1,IF(ISERR(FIND("" "",A1,FIND("" "",A1)+1)),FIND("" "",A1),FIND("" "",A1,FIND("" "",A1)+1))-FIND("" "",A1)-1))" Range("E1:E227").Formula = _ "=RIGHT(A1,LEN(A1)-FIND(""*"",SUBSTITUTE(A1,"" "",""*"",LEN(A1)-LEN(SUBSTITUTE(A1,"" "","""")))))" Columns("C:E").Copy Range("F1").PasteSpecial Paste:=xlPasteValues Columns("B:E").Delete Rows(1).Insert Range("B1:D1").Value = Array("first_name", "middle_initial", "last_name") End Sub
Thanks for response. That gives 0s in the B column down to row 228 and #VALUE! in the D column down to row 228.
I was also working on alternative code. This is my suggestion, which should be tested in a copy of your workbook.
Sub nameSplitter_2()
Application.ScreenUpdating = False
Windows("summary_judgment.xlsx").Activate
With Range("B1:B227")
.Formula = "=IF(A1="""","""",LEFT(A1,FIND("" "",A1)-1))"
.Offset(, 1).Formula = "=IF(A1="""","""",TRIM(SUBSTITUTE(" _
& "SUBSTITUTE(""|""&A1&""|"",""|""&B1,""""),D1&""|"","""")))"
.Offset(, 2).Formula = _
"=IF(A1="""","""",TRIM(RIGHT(SUBSTITUTE(A1,"" "",REPT("" "",100)),100)))"
Rows(1).Insert
Range("B1:D1").Value = Array("first_name", "middle_initial", "last_name")
With .Resize(, 3)
.Value = .Value
.EntireColumn.AutoFit
End With
End With
Application.ScreenUpdating = True
End Sub
Hope this helps, good luck.
Peter
Excel 365 - Windows 10
- Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
- If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker
- Read: Forum Rules, Forum Use Guidelines, & FAQ
Thanks that worked Peter.
Great.
Notice that there are no 'Select' or 'Selection' in my code. Selecting ranges to work with them is a relatively slow process in vba and is rarely necessary, so you should try to eliminate that as much as possible from your code. That often means quite a bit of work if you are starting with a recorded macro, but well worth the trouble.
Hope this helps, good luck.
Peter
Excel 365 - Windows 10
- Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
- If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker
- Read: Forum Rules, Forum Use Guidelines, & FAQ
Like this thread? Share it with others