Solved

search specefic word and copy

Posted on 2012-04-01
6
473 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
ID: 37794441
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
ID: 37794451
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
ID: 37796157
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
Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

 

Author Comment

by:karinos57
ID: 37797675
can onyone help please?  thanks
0
 
LVL 43

Accepted Solution

by:
Saqib Husain, Syed earned 500 total points
ID: 37798976
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
ID: 37815894
thanks
0

Featured Post

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

776 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