Solved

Vertical list to table with hyperlink extraction

Posted on 2011-02-10
6
249 Views
Last Modified: 2012-05-11
I have a list imported from web page (Sheet 2 in the file). I need to parse the data into a table in Sheet 3. Col. A = A7, A15, A23... in Sheet 2; Col. B = hyperlink of A7, A15, A23... in Sheet 2; Col. C= the number in A4, A12, A20... in Sheet 2; Col. D= A5, A13, A21... in Sheet 2; Col. E= the number of A8, A16, A24... in Sheet 2; Col. F= hyperlink of A8, A16, A24... in Sheet 2
As you can see everything is 8 incremental. I have the first two rows in Sheet 3 manually copied.
Can someone help me to build a VBA? Thanks
web-import.xls
0
Comment
Question by:mmcompact
  • 3
  • 2
6 Comments
 
LVL 1

Expert Comment

by:dzenar
ID: 34866083
I think I almost understand the problem - One question -

What do you mean by 'hyperlink of...' do you mean hyperlink TO?
0
 

Author Comment

by:mmcompact
ID: 34866144
yes, so eg. A2 is "sparkzck" from A7 in Sheet2, B2 is "http://store.taobao.com/shop/view_shop.htm?user_number_id=75765428", which is the address you will get when you click on A7 in Sheet2.
And also, when I say "the number of", it means I want to extract the numeric number from the string of whichever cell it belongs
0
 
LVL 30

Accepted Solution

by:
SiddharthRout earned 500 total points
ID: 34866164
Hi

Are you sure that the data is arranged properly in sheet 2?

Click the button on Sheet 2 for the results.

Sid

Code Used


Private Sub CommandButton1_Click()
    Dim i As Long
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim ws1LastRow As Long, ws2LastRow As Long
    
    Set ws1 = Sheets("Sheet2")
    Set ws2 = Sheets("Sheet3")
    
    ws1LastRow = ws1.Range("A" & Rows.Count).End(xlUp).Row
    ws2LastRow = ws2.Range("A" & Rows.Count).End(xlUp).Row + 1
    
    For i = 1 To ws1LastRow Step 8
        On Error Resume Next
        ws2.Range("A" & ws2LastRow).Value = ws1.Range("A" & i).Offset(6).Value
        ws2.Range("B" & ws2LastRow).Value = ws1.Range("A" & i).Offset(6).Hyperlinks(1).Address
        ws2.Range("C" & ws2LastRow).Value = OnlyNumbers(ws1.Range("A" & i).Offset(3).Value)
        ws2.Range("D" & ws2LastRow).Value = ws1.Range("A" & i).Offset(4).Value
        ws2.Range("E" & ws2LastRow).Value = ws1.Range("A" & i).Offset(7).Value
        ws2.Range("F" & ws2LastRow).Value = ws1.Range("A" & i).Offset(7).Hyperlinks(1).Address
        ws2LastRow = ws2LastRow + 1
        On Error GoTo 0
    Next
End Sub

Function OnlyNumbers(strInput As String) As String
    Dim strChar As String, strOutput As String
    strOutput = ""

    For i = 1 To Len(strInput)
        strChar = Mid(strInput, i, 1)
        If (IsNumeric(strChar)) Then
            strOutput = strOutput & strChar
        End If
    Next i
    OnlyNumbers = strOutput
End Function

Open in new window

web-import-1.xls
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 

Author Comment

by:mmcompact
ID: 34866297
@SiddharthRout

Here's the page I want to populate the table from. Is there a way to solve this problem?
http://s.taobao.com/search?q=ugg&commend=all&style=list&tab=all&uniq=seller&ssid=s5-e&sort=sale-desc
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34866335
mmcompact: I have no issues in sharing points if someone has helped but I couldn't understand your choice for the "Assisted Solution"? How does that assist you in your query?

Sid
0
 

Author Comment

by:mmcompact
ID: 34872764
When I accepted your solution, the site didn't let me assign all the 500 points to you, I must pick one more. So I just put your 20 points to the other guy. I don't know another way to go around it. Kinda new here, sorry
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
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…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

744 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

11 Experts available now in Live!

Get 1:1 Help Now