Take data, replace function, execute marco, copy data and paste. Then loop

[Do Duy]

Hi all, please help me to solve this problem.

I have 1 file CSV and 1 file Summary excel. I need someone help me to make some action as below

Step 1 : Using Replace function to replace "*<OTA_AirLowFareSearchRQ" = "<OTA_AirLowFareSearchRQ" ( I just want to delete data before this tag).

Step 2 : Copy data from cell Q2 of CSV file

Step 3 : Paste in to sheet "sample" , cell B2 of excel file and run this marco

Sub Demo1()
With Worksheets("sample")
        SPQ = Split(Replace(.[B2].Value, ">", ">?"), "?")
         U& = UBound(SPQ)
    While R& < U
        If SPQ(R) Like "<*" Then
                     R = R + 1
        Else
            SPQ(R - 1) = SPQ(R - 1) & SPQ(R)
                     U = U - 1
            For N& = R To U:  SPQ(N) = SPQ(N + 1):  Next
        End If
    Wend
    For R = 0 To U - 1
        If SPQ(R) Like "</*" Then
                S$ = Left$(S$, Len(S$) - 2)
            SPQ(R) = S & SPQ(R)
        Else
                ST = Split(SPQ(R), "<")
            SPQ(R) = S & SPQ(R)
            If Not (SPQ(R) Like "*/>" Or ST(UBound(ST)) Like "/*") Then S = S & "  "
        End If
    Next
        .[E2].Resize(U).Value = Application.Transpose(SPQ)
End With
End Sub

Step 4 : copy all data after process from cell E2 -> end data from sheet "sample" to cell G1 of sheet "process"
Step 5 : run marco in sheet "process"
Step 6 : paste precessed data to cell E8 of sheet "Summary"
Step 7 : Loop from step 2 with cell Q3 and paste processed data to F8...and so on

Thank.

 

[Deepak]

Ask it here!

http://forum.chandoo.org/threads/process-data-when-found-character-and-keep-parse.26755