VBA Hyperlink Value in range based off another range

Question

I have the following code that will automatically hyperlink cells in column A starting at row 5, to the same named file in a folder. I would like this to only do this based off of what the Value in column B of the same row would be. I am trying to figure out and if statement so it will only hyperlink in rows which column B = "Blue". Any guidance would be great

Dim cell As Range

For Each cell In Range(Range("A5"), Range("A5").End(xlDown)) ActiveSheet.Hyperlinks.Add cell, "L:\Supplier Invoices\Blue Invoices" & cell.Value & ".pdf"

Next End Sub

I cannont figure out where how to accomplish this.

VBasic2008 · Accepted Answer · 2023-06-27 07:11:23Z

Add Hyperlinks For Matches in Another Column

Sub LinkBlueInvoices()
    
    Const FIRST_CELL As String = "A5"
    Const CRITERIA_COLUMN As String = "B"
    Const CRITERIA_STRING As String = "Blue"
    Const FOLDER_PATH As String = "L:\Supplier Invoices\Blue Invoices\"
    Const FILE_EXTENSION As String = ".pdf"
    Const MSG_NO_DATA As String = "No data found."
    Const MSG_SUCCESS As String = "Blue invoices linked."
    
    Dim ws As Worksheet: Set ws = ActiveSheet ' improve!
    
    Dim hrg As Range, rCount As Long
    
    With ws.Range(FIRST_CELL)
        rCount = ws.Cells(ws.Rows.Count, .Column).End(xlUp).Row - .Row + 1
        If rCount > 0 Then
            Set hrg = .Resize(rCount)
        End If
    End With
        
    If hrg Is Nothing Then
        MsgBox MSG_NO_DATA, vbCritical
        Exit Sub
    End If
    
    Application.ScreenUpdating = False
    
    hrg.Hyperlinks.Delete ' delete existing hyperlinks!?
    
    Dim crg As Range: Set crg = hrg.EntireRow.Columns(CRITERIA_COLUMN)
        
    Dim hCell As Range, cCell As Range, r As Long
    Dim hString As String, cString As String, FilePath As String
    
    For Each hCell In hrg.Cells
        r = r + 1
        hString = CStr(hCell.Value)
        If Len(hString) > 0 Then
            Set cCell = crg.Cells(r)
            cString = CStr(cCell.Value)
            If StrComp(cString, CRITERIA_STRING, vbTextCompare) = 0 Then
                FilePath = FOLDER_PATH & hString & FILE_EXTENSION
                ws.Hyperlinks.Add hCell, FilePath
            End If
        End If
    Next hCell

    Application.ScreenUpdating = True
    
    MsgBox MSG_SUCCESS, vbInformation

End Sub

kevin · Accepted Answer · 2023-06-26 20:39:58Z

0

You want to check if the string contains another string. Use InStr.

The following assumes you do not need a case-sensitive match for "blue":

Sub testsub()

Dim cell As Range

For Each cell In Range(Range("A5"), Range("A5").End(xlDown))
    If InStr(1, cell, "blue", vbTextCompare) Then
        ActiveSheet.Hyperlinks.Add cell, "L:\Supplier Invoices\Blue Invoices" & cell.Value & ".pdf"
    End If
Next

End Sub

You might want to add another \ after Blue Invoices.

And it's common practice to avoid using cell as the name of a variable because it's so close to the restricted name Cells. It's more common to use r or rg or rng for the name of a range variable.

answered Jun 26, 2023 at 20:39

kevin

2,1981 gold badge6 silver badges13 bronze badges

Collectives™ on Stack Overflow

VBA Hyperlink Value in range based off another range

2 Answers 2

Add Hyperlinks For Matches in Another Column

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Add Hyperlinks For Matches in Another Column

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related