Improve company productivity with a Business Account.Sign Up

x
?
Solved

Vertical list to table with hyperlink extraction

Posted on 2011-02-10
6
Medium Priority
?
263 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 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("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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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.

 

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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

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.

Join & Write a Comment

What to do if a split doesn't fit? Or a bunch of invoice lines must be rounded while the sum must match a total? It takes a little, but - when done - it is extremely easy to implement.
This article presents several of my favorite code snippets.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

606 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