search specefic word and copy

Hi,
I have been looking for solution for my problem for long time but not successful so far.. I would like to use some kind of vba code to go thru line by line in the attached excel file and look for specific words then when found these words, copy them and paste them in sheet2.   Here is what I am trying to do in detail:
First I want to find a word that starts like this:
 web_
if the next line after the word    web_   starts either:
 URL=    
or
action=
then copy everything after the word action= or URL= and insert in sheet2
for instance if you have this in the file then I need to copy everything after the URL=   which is this link https://dataupload.ajk.com/ and past them in sheet2 :
web_url("dataupload.ajk.com",
      URL=https://dataupload.ajk.com/

there are so many url's in the file but i am only interested the one and only the one that comes after the word  web_
please see attached file and i highlighted all the words... thanks
test.xls
karinos57Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

suvmitraCommented:
Hi,

Please find attached the Macro File. Run the Copy_text Macro from the file.

Procedure of running the Macro:

1. Clear Sheet1 Data.
2. Paste the New Data in the same manner. Notice when you paste the Data "URL=", "Action=" are coming in Colume 3. Marco will be looking the data from Column3 only.

3. Code is by default set to pick up only   Data "URL=", "Action=".

4. Press ALT + F8 to invoke Macro Window .. select teh Copy_Text Macro and Run.
test-done.xls
0
Saqib Husain, SyedEngineerCommented:
Try this macro

Sub weburl()
    Dim webf As Range, urlf As Range, webfp As Range
    Set webfp = Range("A1")
    Set webf = ActiveSheet.UsedRange.Find("web_")
    Set urlf = webf.Offset(1, 1)
    Do While webf.Row > webfp.Row
        Set webfp = webf
        If Left(urlf, 4) = "URL=" Then
                Sheet2.Range("A" & Rows.Count).End(xlUp).Offset(1, 0) = Right(urlf, Len(urlf) - 4)
            ElseIf Left(urlf, 7) = "Action=" Then
                Sheet2.Range("A" & Rows.Count).End(xlUp).Offset(1, 0) = Right(urlf, Len(urlf) - 7)
        End If
        Set webf = ActiveSheet.UsedRange.Find("web_", webf)
        Set urlf = webf.Offset(1, 1)
    Loop
End Sub
0
karinos57Author Commented:
Thanks  ssaqibh, your method works perfect but would like to ask you one more thing that I forgot to mention earlier.  Each URL has step name associated with it and here is how you can find it:
If you look at one or 2 lines above the word   web_    you will see something like this:
lr_start_transaction("Step01-Load Login");

so what I want to do is put the  step name is column A in sheet2 and the URL in column B in Sheet2.  Something like this:

ColumnA                                         ColumnB
Step01-Load Login                  https://dataupload.ajk.com/
Step03-Import Utility Tab     https://dataupload.ajk.com/dmui/pages/start.jsf
Step04-Conversion Tab       https://dataupload.ajk.com/dmui/pages/fileupload.jsf

Thanks for your effort and time…
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

karinos57Author Commented:
can onyone help please?  thanks
0
Saqib Husain, SyedEngineerCommented:
Try

Sub weburl()
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim webf As Range, urlf As Range, webfp As Range, stepf As Range
    Set ws1 = Worksheets("Sheet1")
    Set ws2 = Worksheets("Sheet2")
    Set webfp = ws1.Range("A1")
    Set webf = ws1.UsedRange.Find("web_", , , , , xlNext)
    Set urlf = webf.Offset(1, 1)
    Do While webf.Row > webfp.Row
        Set webfp = webf
        If Left(urlf, 4) = "URL=" Or Left(urlf, 7) = "Action=" Then
                ws2.Range("B" & Rows.Count).End(xlUp).Offset(1, 0) = Right(urlf, Len(urlf) - IIf(Left(urlf, 4) = "URL=", 4, 7))
                Set stepf = ws1.UsedRange.Find("lr_start_transaction", webf, , , , xlPrevious)
                ws2.Range("B" & Rows.Count).End(xlUp).Offset(0, -1) = Replace(Replace(stepf, "lr_start_transaction(""", ""), """)", "")
        End If
        Set webf = ws1.UsedRange.Find("web_", webf, , , , xlNext)
        Set urlf = webf.Offset(1, 1)
    Loop
End Sub
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
karinos57Author Commented:
thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.