FIND ROW TITLE AND INSERT COLUMNS 1

[Evil8]

Hello,

I'm working in Excel 2003. I have reports that get dumped into worksheets. The problem is that names(Client Name) are in 1 column as last, first format. I need to separate them out into 2 columns of Last and First. Some reports the Client Name column is in Column A and in some they end up in another column. I could create individual macros for each report, but instead I would like to create one macro that will look for the title text string as a variable txt_TCName.

Here is the initial macro I developed:

' SeparateName Macro
' Create first name and last name from full name.
'
Dim LastRow As Long, r As Long 'Last Row

'Find the LAST real row
LastRow = ActiveSheet.Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByRows).Row
'
Columns("B:B").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("B:E").Select
Selection.ColumnWidth = 20
Range("B1").Select
ActiveCell.FormulaR1C1 = "Last Name"
With ActiveCell.Characters(Start:=1, Length:=9).Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 10
End With
Range("B2").Select
ActiveCell.FormulaR1C1 = "=LEFT(RC[-1],FIND("","",RC[-1])-1)"
Range("B2").Select
Selection.AutoFill Destination:=Range("B2:B" & LastRow), Type:=xlFillDefault
Range("B2:B" & LastRow).Select

Range("D1").Select
ActiveCell.FormulaR1C1 = "First Name"
With ActiveCell.Characters(Start:=1, Length:=10).Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 10
End With
Range("D2").Select
ActiveCell.FormulaR1C1 = _
"=RIGHT(RC[-3],LEN(RC[-3])-FIND(""*"",SUBSTITUTE(RC[-3],"","",""*"",LEN(RC[-3])-LEN(SUBSTITUTE(RC[-3],"","","""")))))"
Range("D2").Select
Selection.AutoFill Destination:=Range("D2" & LastRow), Type:=xlFillDefault
Range("D2" & LastRow).Select
Range("B1:B" & LastRow).Select
Selection.Copy
Range("C1").Select
Selection.PasteSpecial Paste:=xlPasteValues
Range("D1" & LastRow).Select
Application.CutCopyMode = False
Selection.Copy
Range("E1").Select
Selection.PasteSpecial Paste:=xlPasteValues
Columns("D").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Columns("A:B").Select
Range("B1").Activate
Selection.Delete Shift:=xlToLeft
Range("C12").Select

All help is really appreaciated.

 

[SkipVought]

Hi,

I need to separate them out into 2 columns of Last and First.

Check out Data > Text to columns -- DELIMITED on COMMA

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Evil8]

Skip,

My question has to do with searching for the colunm name text string and identifiying that cell.

I have this:
Columns("B:B").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight

What I need to identify the correct column and assign it to a variable because it's not always going to be Columns("B:B").Select

This finds the text: Cells.Find(What:="Client Name", SearchOrder:=xlByColumns, MatchCase:=True).Activate

Now how do I insert 4 columns to the right of it? I tried:

ActiveCell.Offset(ColumnOffset:=1).Activate

And then Selection.Insert Shift:=xlToRight

But that didn't move the whole column only the cells in the active row. This is the current point I'm stuck at the moment.

Thanks!

 

[PHV]

Did you try to replace this:
Columns("B:B").Select
with this ?
Cells.Find(What:="Client Name", SearchOrder:=xlByColumns, MatchCase:=True).EntireColumn.Select

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Evil8]

PHV,

Yes, that works but in the greater idea here I need to keep refencing dynamic columns. As per my initial post I have to add formulas into some columns then copy/paste the formula values into the others and delete the un-needed ones.

So shouldn't I Dim a variable, assign the variable and then use Columns( ).Select?

Dim NCol As Long
NCol = Cells.Find(What:="Client Name", SearchOrder:=xlByColumns, MatchCase:=True).Activate
Columns(NCol).Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight

Obviously the line Columns(NameCol).Select doesn't work. What would be the proper syntax? Or am I way off base here?

Again thanks.

 

[PHV]

NCol = Cells.Find(What:="Client Name", SearchOrder:=xlByColumns, MatchCase:=True).[!]Column[/!]

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Evil8]

PHV That was GREAT! That worked, thanks. I don't do enough of these to remember all these nuances so resourses like this site are invaluble. I'm done for the day, but back to grind to finish this project tomorrow.