Preserving Hyperlink during a Lookup - vba code?

[mageeg]

Hi, i am wondering if this possible.

I have created a little database within excel.

One table i have in this database is the following (see below). These "From and "To" fields are for a serial number. The user enters a serial number on a userform, and it pulls through the row from this table in which the serial number falls into, and puts it in my sheet named "Database".

The issue you can see is that column H is a hyperlink to a PDF file. If we take H1 for example, any serial number within 2200487 and 2200546 will use this PDF.

I can bring this data through to the database simply like this:

Dim ID As Long
Dim StartID As Long
Dim EndID As Long
Dim i As Range
Dim Flg As Boolean


If SerialNumber.Value = "" Then

Else

'code for receiver range, *if number entered is between range then lookup rest of data*


    ID = SerialNumber.Value
  
        For Each i In Sheets("ReceiverData").Range("Table2[From]")
      
        StartID = i.Value
        EndID = i.Offset(0, 1).Value
      
            If ID >= StartID And ID < EndID Then

            txtType.Value = i.Offset(0, 3).Value
            txtSize.Value = i.Offset(0, 4).Value
            txtWKPRESS.Value = i.Offset(0, 6).Value
            txtCertDate.Value = i.Offset(0, 5).Value
            TextBox26.Value = i.Offset(0, 7).Value     'Certificate hyperlink
            Flg = True
            End If
      
        Next i

End If

I then add this information to the database like so:

Dim sh As Worksheet
    Dim iRow As Long
 
    Set sh = ThisWorkbook.Sheets("Database")
    
    
    If frmForm.txtRowNumber.Value = "" Then
    
        iRow = [Counta(Database!A:A)] + 1
    Else
    
        iRow = frmForm.txtRowNumber.Value
        
    End If
    
    'Duplicate checking for Works Order Number
    
    
    
    With sh
    
    'adding each row to database
    
        .Cells(iRow, 1) = "=Row()-1" 'Dynamic Serial Number
        
        .Cells(iRow, 2) = frmForm.ModelNo.Value
        
        .Cells(iRow, 3) = frmForm.PartNo.Value
        
        .Cells(iRow, 4) = frmForm.WorksOrderNo.Value
        
        .Cells(iRow, 5) = frmForm.SerialNo.Value
        
        .Cells(iRow, 6) = frmForm.MaterialNo.Value
        
        .Cells(iRow, 7) = frmForm.SerialNumber.Value
        
        .Cells(iRow, 8) = frmForm.txtType.Value
        
        .Cells(iRow, 9) = frmForm.txtSize.Value
        
        .Cells(iRow, 10) = frmForm.txtWKPRESS.Value
        
        .Cells(iRow, 11) = frmForm.txtCertDate.Value
        
        .Cells(iRow, 12) = frmForm.BatchNo.Value
        
        .Cells(iRow, 13) = frmForm.JobNo.Value
        
        .Cells(iRow, 14) = frmForm.DateOfManufacture.Value
        
        .Cells(iRow, 15) = frmForm.Label47.Caption
        
        .Cells(iRow, 16) = Application.UserName
        
        .Cells(iRow, 17) = [Text(Now(), "DD-MM-YYYY HH:MM:SS")]
        
        .Cells(iRow, 19) = frmForm.TextBox26.Value
        
    End With

This works and pulls through the correct certificate. However obviously, it does not include the hyperlink with it. I would like the hyperlink to be included if it is possible. As i am going to have a button to automatically print it depending on what serial number was entered into the database

 

[severynm]

Use the Hyperlinks.Add method of the worksheet whenever you are writing to your table. You'll need to provide the anchor (which cell is to contain the hyperlink), hyperlink address, and the friendly name for the link. Example:

Me.Hyperlinks.Add Anchor:=Me.Range("A1"), Address:="C:\Temp\_CB2TMPDATA.csv", TextToDisplay:="1"