Solved

search specefic word and copy

Posted on 2012-04-01
6
467 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

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…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

910 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

22 Experts available now in Live!

Get 1:1 Help Now