Solved

search specefic word and copy

Posted on 2012-04-01
6
462 Views
Last Modified: 2012-04-06
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
0
Comment
Question by:karinos57
  • 3
  • 2
6 Comments
 
LVL 9

Expert Comment

by:suvmitra
Comment Utility
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
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
Comment Utility
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
 

Author Comment

by:karinos57
Comment Utility
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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

Author Comment

by:karinos57
Comment Utility
can onyone help please?  thanks
0
 
LVL 43

Accepted Solution

by:
Saqib Husain, Syed earned 500 total points
Comment Utility
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
 

Author Closing Comment

by:karinos57
Comment Utility
thanks
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Not long ago I saw a question in the VB Script forum that I thought would not take much time. You can read that question (Question ID  (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28455246.html)28455246) Here (http…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now