Share via

How to make table in excel using hyperlinks in excel?

Anonymous
2024-12-20T21:39:10+00:00


Hello, just very interested in how to make table by VBA that takes information from hyperlinks and inserts it in table . So the task asks to:

1)Create a VBA procedure that creates a data table on the Table worksheet based on the data from the European Parliament worksheet with columns: Name, country, party/faction (definitely in this order).

2)Hyperlinks in the data should also appear in the ordered table (ie, the cells should be copied).
3)Color the background of the row cells of the result table red (index 3) if the member of parliament belongs to the Uueneva Euroopa fraktsiooni
4)The procedure must first remove the previous data from the result table (headers remain)

On image you can see hyperlinks.
Would appreciate the help!

Microsoft 365 and Office | Excel | For education | Other

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2024-12-21T13:55:31+00:00

    Could you share a test file with some dummy data in it show one of the expected result? Then I will have a try on it.

    For sharing file, you may upload it to OneDrive or any other cloud drive and then post the link here.  *Please make sure you have removed any sensitive or private information in the sample file before uploading.

    0 comments
  2. Anonymous
    2024-12-21T18:36:56+00:00
    0 comments
  3. Anonymous
    2024-12-23T06:37:48+00:00

    Could you show serveral rows of expeted result?

    You may use formula to convert it to table.

    =CHOOSECOLS(WRAPROWS(C2:C3596,5),1,2,3,4)

    Then paste formula as value and use macro to create same hyperlink based on name found in Column C.

    Sub CreateHyperlinks() 

    Dim ws As Worksheet 

Dim lastRowH As Long 

Dim lastRowC As Long 

Dim cellH As Range 

Dim cellC As Range 

Dim found As Range 

' Set the worksheet 

Set ws = ThisWorkbook.Sheets("European Parlament") ' Change "Sheet1" to your sheet name 

' Find the last row in columns H and C 

lastRowH = ws.Cells(ws.Rows.Count, "H").End(xlUp).Row 

lastRowC = ws.Cells(ws.Rows.Count, "C").End(xlUp).Row 

' Loop through each cell in column H 

For Each cellH In ws.Range("H1:H" & lastRowH) 

    ' Find the corresponding cell in column C 

    Set found = ws.Range("C1:C" & lastRowC).Find(What:=cellH.Value, LookIn:=xlValues, LookAt:=xlWhole) 

    ' If found, create the hyperlink in column H 

    If Not found Is Nothing Then 

        ws.Hyperlinks.Add Anchor:=cellH, Address:=found.Hyperlinks(1).Address, TextToDisplay:=cellH.Value 

    End If 

Next cellH

    End Sub

    0 comments