Solved

search specefic word and copy

Posted on 2012-04-01
6
477 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

707 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