Pull out names from a string, into multiple columns?

HockeyDiablo

Board Regular
Joined
Apr 1, 2016
Messages
180
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

Board Regular
Joined
Apr 1, 2016
Messages
180
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

Well-known Member
Joined
Jun 24, 2015
Messages
1,647
One approach to looping is the For/Next construct as in the example below...

Code:
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
 

Forum statistics

Threads
1,086,235
Messages
5,388,616
Members
402,128
Latest member
Jemx

Some videos you may like

This Week's Hot Topics

Top