Add multiple columns with string data from another column

Question

This program places a name in Column E from a string in Column D. How can I introduce a Column F for strings with 2 names? Could Column G work for a 3rd?

Sub name1() 'Main Program

Dim nmArr()
Dim i As Long
Dim cl As Range

Set cl = ActiveSheet.Range("D2") '## This is the STARTING cell

'## This is the list of names built as an array
nmArr = Array("Christy", "Kari", "Sue", "Clayton", "DanK", "Gawtry", Holly", "John", "Matt", "Dustin", "David")
Do
    For i = LBound(nmArr) To UBound(nmArr)
        If InStr(1, cl.Value, nmArr(i), vbTextCompare) Then
            cl.Offset(0, 1).Value = nmArr(i)
            Exit For
        End If
    Next

    '## Get a handle on the NEXT cell
    Set cl = cl.Offset(1, 0)
Loop Until Trim(cl.Text) = vbNullString
outcome1

End Sub

@Scottcramer : Here is what my data looks like...

So let me get this straight, you have a column that includes first names, last names and possibly a middle name? And you want to pull the various names out and populate the next three columns with those each in their own cell? Can you show some example data? There may be an easier way than the route your currently following. — Scott Craner
– Scott Craner, Commented Apr 21, 2016 at 17:41

Scott Craner · Accepted Answer · 2016-04-21 18:01:17Z

1

This will split the data on the , then test each part if it is in the array and output each name in either E,G,H

Sub name1dd() 'Main Program

Dim nmArr()
Dim i As Long, j As Integer
Dim cl As Range
Dim splArr() As String
Dim nm As Variant

Set cl = ActiveSheet.Range("D2") '## This is the STARTING cell

'## This is the list of names built as an array
nmArr = Array("Christy", "Kari", "Sue", "Clayton", "DanK", "Gawtry", "Holly", "John", "Matt", "Dustin", "David")

Do
    j = 1
    splArr = Split(cl.Value, ",")
    For Each nm In splArr
        For i = LBound(nmArr) To UBound(nmArr)
            If InStr(1, nm, nmArr(i), vbTextCompare) Then
                cl.Offset(0, j).Value = nmArr(i)
                If j = 1 Then
                    j = j + 2
                Else
                    j = j + 1
                End If
                Exit For
            End If
        Next i
    Next nm

    '## Get a handle on the NEXT cell
    Set cl = cl.Offset(1, 0)
Loop Until Trim(cl.Text) = vbNullString
outcome1

End Sub

answered Apr 21, 2016 at 18:01

Scott Craner

154k10 gold badges52 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

J St Over a year ago

Compile Error: For each control variable on arrays must be variant?

J St Over a year ago

That totally did it, very impressive. Thank you kindly. You have no idea how much that helped and how much it help me understand the whole process.

J St Over a year ago

Sorry, new here! Thanks again!

findwindow Over a year ago

Didn't use formulas but I guess still worth upvote =P

Scott Craner Over a year ago

@findwindow almost suggested it, till I noticed that they call another sub from this one.

|

Collectives™ on Stack Overflow

Add multiple columns with string data from another column

1 Answer 1

10 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

10 Comments

Your Answer

Sign up or log in

Post as a guest

Related