Solved

Vertical list to table with hyperlink extraction

Posted on 2011-02-10
6
253 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
Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

 

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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering 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

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…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

809 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