Pull out names from a string, into multiple columns?

[HockeyDiablo]

Trying to run a quick script to pull a few names out of a string and place them in the column next to the string location. Cant seem to get this going. Any tips would be greatly appreciated.

Sub james()
Dim celltxt As String
celltxt = ActiveSheet.Range("d4").Text


If InStr(1, celltxt, "Sue") Then
Range("e4").Value = "Sue"

ElseIf InStr(1, celltxt, "Kari") Then
Range("e4").Value = "Kari"

ElseIf InStr(1, celltxt, "Holly") Then
Range("e4").Value = "Holly"






Else


MsgBox ("error")
james2
End If
End Sub


Sub james2()
Dim celltxt2 As String
celltxt = ActiveSheet.Range("d4").Text


If InStr(1, celltxt2, "Sue") And Range("e4").Value <> "Sue" Then
Range("g4").Value = "Sue"

ElseIf InStr(1, celltxt2, "Kari") And Range("e4").Value <> "Kari" Then
Range("g4").Value = "Kari"

ElseIf InStr(1, celltxt2, "Holly") And Range("e4").Value <> "Holly" Then
Range("g4").Value = "Holly"






Else




End If
End Sub

Thanks again.

James

 

[HockeyDiablo]

I think I have got it... seems to work great on testing, now if I can only figure out how to create a loop to do the next row....

Sub james()
Dim celltxt As String
celltxt = ActiveSheet.Range("A1").Value

If InStr(1, celltxt, "Sue", vbTextCompare) Then
Range("B1").Value = "Sue"

ElseIf InStr(1, celltxt, "Kari", vbTextCompare) Then
Range("B1").Value = "Kari"

ElseIf InStr(1, celltxt, "Holly", vbTextCompare) Then
Range("B1").Value = "Holly"

Else

MsgBox ("error")
End If
james2

End Sub
_________________________________________

Sub james2()

Dim celltxt As String
celltxt = ActiveSheet.Range("A1").Value


If InStr(1, celltxt, "Sue") And ActiveSheet.Range("b1").Value <> "Sue" Then
Range("C1").Value = "Sue"

ElseIf InStr(1, celltxt, "Kari") And ActiveSheet.Range("b1").Value <> "Kari" Then
Range("C1").Value = "Kari"

ElseIf InStr(1, celltxt, "Holly") Then
Range("C1").Value = "Holly"

Else

MsgBox ("error")
End If

End Sub

 

[tonyyy]

One approach to looping is the For/Next construct as in the example below...

Sub james2()

Dim celltxt As String
[COLOR=#0000ff]Dim i As Long[/COLOR]

[COLOR=#0000ff]For i = 1 To 10[/COLOR]
    celltxt = ActiveSheet.Range("A" & [COLOR=#0000ff]i[/COLOR]).Value
    If InStr(1, celltxt, "Sue") And ActiveSheet.Range("b" & [COLOR=#0000ff]i[/COLOR]).Value <> "Sue" Then
        Range("C" & [COLOR=#0000ff]i[/COLOR]).Value = "Sue"
    ElseIf InStr(1, celltxt, "Kari") And ActiveSheet.Range("b" & [COLOR=#0000ff]i[/COLOR]).Value <> "Kari" Then
        Range("C" & [COLOR=#0000ff]i[/COLOR]).Value = "Kari"
    ElseIf InStr(1, celltxt, "Holly") Then
        Range("C" & [COLOR=#0000ff]i[/COLOR]).Value = "Holly"
    Else
        MsgBox ("error")
    End If
[COLOR=#0000ff]Next i[/COLOR]

End Sub

Cheers,

tonyyy