VBA Code to split cell's text data

Hi,
Here is macro that
split value in column F
populate column A
Populate column E
populate B with VLOOKUP formula that retrieves data from the other sheet

Sub SplitString()
    Dim cl As Range
    Dim vSplitString As Variant
    Dim intIndex As Integer
    Dim strName As String
    Dim rngEMails As Range
    
    With Worksheets("Email ID Data")
        Set rngEMails = .Range("A1").Resize(.Range("A" & _
                                                   Rows.Count).End(xlUp).Row, 2)
    End With

    For Each cl In Range("F2:F" & Range("F" & Rows.Count).End(xlUp).Row)
        vSplitString = VBA.Split(cl.Value, "\")
        intIndex = UBound(vSplitString)
        strName = vSplitString(intIndex)
        Range("A" & cl.Row).Value = Left(strName, InStrRev(strName, ".") - 1)    'Name
        Range("E" & cl.Row).Value = vSplitString(intIndex - 1)  'Subject
        Range("B" & cl.Row).FormulaR1C1 = "=VLOOKUP(RC1," & _
            rngEMails.Address(True, True, xlR1C1, True) & ",2,False)"
    Next cl

End Sub

Originally Posted by Liz_Biz

Is it possible to split path like "C:\Documents and Settings\liz\Desktop\INST\UID_August_Baker,Edward D.xlsx" into two parts?
Name (Column A) "Baker,Edward D" and Subject (Column E) "UID_August". Rest condition would be same.

Yes, it's possible but you should specify where to split (e.g. at last underscore)

Originally Posted by Liz_Biz

Also we can't use vlookup as this fails once it search same value again.

I don't understand waht you mean with that. Can you explain further. You can always convert formula to value...