vba pulling names from fields using instr and left

Question

Me again everyone. I am trying to pull various strings and place a string in a separate field. I cannot seem to get my code to work and I am hoping someone can shine some light in my flawed attempts. Thank you very much.

I have column "B" which includes multiple names and is in the following format:

       B                    C         D
   Other Team            teamOne     teamTwo
 /jdoe/smithjr/
 /someguy/testuser/
 /obiwan/luke/darth
 /vader/
 /hp/dell/lenova/mint/

I only need to take the first two names and place one name in one field and one name in another field.

Expected results

       B                    C         D
   Other Team            teamOne     teamTwo
 /jdoe/smithjr/            jdoe       smithjr
 /someguy/testuser/       someguy     testuser
 /obiwan/luke/darth        obiwan     luke
 /vader/                   vader
 /hp/dell/lenova/mint/     hp         dell

The code I have come up with so far is below and does not work. I receive errors that there is no data to replace.

For Q = 2 To 10
If UCase(Cells(Q, "B").Value) Like "*Other Team*" Then
  Name = Cells(Q, "B").Value
  startingPosition = InStr(Name, "/")
  Firstname = Left(Name, (startingPosition) - 1)
  secondName = Right(Name, startingPosition - 2)
  Cells(Q, "C").Value = Firstname
  Cells(Q, "D").Value = secondName
End If
Next Q

I am pretty new to VBA (on 3rd day) and cannot seem to figure out how to parse this data. Perhaps someone can explain what I am doing wrong and assist me? Thank you.

Answer 1

just to put in a "formula" approach

Sub main()

If UCase$(Cells(1, "B").Value) Like "*OTHER TEAM*" Then
    Range("c2:c10").FormulaR1C1 = "=MID(RC2,2,SEARCH(""/"",RC2,2)-2)"
    Range("d2:d10").FormulaR1C1 = "=IF(ISNUMBER(SEARCH(""/"",RC2,SEARCH(""/"",RC2,2)+1)),MID(RC2,SEARCH(""/"",RC2,2)+1,SEARCH(""/"",RC2,SEARCH(""/"",RC2,2)+1)-SEARCH(""/"",RC2,2)-1),"""")"
End If

a more "clear" format of which could be

Dim str1 As String, str2 As String, formula1 As String, formula2 As String

str1 = "SEARCH(""/"",RC2,2)"
str2 = "SEARCH(""/"",RC2," & str1 & "+1)"
formula1 = "=MID(RC2,2," & str1 & "-2)"
formula2 = "=IF(ISNUMBER(" & str2 & "),MID(RC2," & str1 & "+1," & str2 & "-" & str1 & "-1),"""")"

If UCase$(Cells(1, "B").Value) Like "*OTHER TEAM*" Then
    Range("c2:c10").FormulaR1C1 = formula1
    Range("d2:d10").FormulaR1C1 = formula2
End If

Answer 2

Since you have a delimited string, I'd personally use the Split function. Note also that your If UCase(Cells(Q, "B").Value) Like "*Other Team*" Then test fails on every input in your sample data - it is only true for the column header that you skip, assuming that you change the test to uppercase like you do the input. If you're trying to determine if you're on the correct worksheet, it needs to go outside of the loop.

Something like this:

If UCase$(Cells(1, "B").Value) Like "*OTHER TEAM*" Then
    Dim tokens() As String
    For Q = 2 To 10
        Name = Cells(Q, "B").Value
        tokens = Split(Name, "/")
        If UBound(tokens) > 0 Then Cells(Q, "C").Value = tokens(1)
        If UBound(tokens) > 1 Then Cells(Q, "D").Value = tokens(2)
    Next Q
End If