none
Parse cells using VBA in Excel

    Question

  • I have a wroksheet with values that I need to parse into a header and a piece of data. I looks like this:

    ["t_hours"] = 0,  
    ["f_online"] = "5 |4day:days;",  
    ["f_zone"] = "Stranglethorn Vale",  
    ["f_class"] = "Hunter",  
    ["f_rankIndex"] = 2,  
    ["f_guild"] = "Triple Helix",  
    ["f_officernote"] = "Novs",  
    ["f_id"] = 132,  
    ["f_rank"] = "Officer Alt",  
    ["f_hours"] = 132,  
    ["f_note"] = " ",  
    ["f_expand"] = 0,  
    ["f_lvl"] = 38,  
    ["f_isAlt"] = 1,  
    ["f_alt"] = "Novs",  
    ["f_p_note"] = " ",  
    ["f_checked"] = 0,  
    ["f_name"] = "Novrod",  
     
    I was using code like:

    With Application.WorksheetFunction
                    cellValue = myCell.Offset(j, 1).Value
                    header = Mid(cellValue, 3, .Search(cellValue, "=", 1) - 2)
                    data = Mid(cellValue, .Search((cellValue), "=", 1) + 3, Len(cellValue) - 2)

    but on the header line I get an error:

    Runtime error 1004

    Unable to get Search Property of the WorksheetFunction class.

    What I need to happen, if the code is on the last line of the example, is:

    header = f_name
    and data = Novrod

    Thanks for looking.
    Saturday, March 14, 2009 5:20 PM

All replies

  • Please Try this procedure.

    Sub parseCell()
    Dim PT As Range
    Dim header As String
    Dim data
        Set PT = ThisWorkbook.Worksheets("Sheet1").Range("a1")
        Do Until PT = ""
            data = VBA.Split(PT.Value, "=")
            header = VBA.Split(data(0), """")(1)
            data = VBA.Replace(data(1), """", "")
            data = VBA.Replace(data, ",", "")
            MsgBox "header=" & header & vbCrLf & "data=" & data
            Set PT = PT.Offset(1, 0)
        Loop
    End Sub
    Sunday, March 15, 2009 4:00 AM