x
Solved

# Vertical list to table with hyperlink extraction

Posted on 2011-02-10
Medium Priority
263 Views
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
Question by:mmcompact
• 3
• 2

LVL 1

Expert Comment

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

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

SiddharthRout earned 2000 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("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
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
``````
web-import-1.xls
0

Author Comment

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

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

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

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.