A shorter way to test a range for a specific string

Question

Trying to create a code that will export my excel invoice sheet to PDF, at the specified file path. The path is based on whether the invoice list contains a specific ProductX product.

This is what I came up with, but it seems cumbersome to iterate over each individual cell in the range to see if there is ProductX.

Is there an easier way to do this? Appreciate any help!

Sub ExportToPDF()
'
Dim file_path As String
Dim search_range As Range
Dim each_cell As Range

' Set search_range as desired search range
Set search_range = ActiveSheet.Range("A53:R56")

For Each each_cell In search_range.Cells
    If InStr(1, each_cell.Value, "ProductX", vbTextCompare) Then
        file_path = Some_path_A
    Else: file_path = Some_path_B
    End If
Next each_cell

'Export the sheet as PDF
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:=file_path, Quality:=xlQualityStandard _
        , IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
        True
End Sub

Answer 1

You can use Findfor partial matching.

This code assumes that the return path contains the filepath variable that you need — you may need to configure it.

Dim rng1 As Range
Set rng1 = ActiveSheet.Range("A53:R56").Find("ProductX", , xlFormulas, xlPart)
If rng1 Is Nothing Then Exit Sub

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=rng1.Value, Quality:=xlQualityStandard _
        , IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
        True

Answer 2

, brettdj, , ...

Sub ExportToPDF()
    Dim file_path As String
    Dim search_range As String
    Dim each_cell As Range
    Dim rng1 As Range
    ' Set search_range as desired search range
    search_range = ActiveSheet.Range("A53:R56")


    Set rng1 = ActiveSheet.Range("A53:R56").Find("ProductX", , xlFormulas, xlPart)
    If Not rng1 Is Nothing Then
        file_path = Some_path_A
    Else
        file_path = Some_path_B
    End If

    'Export the sheet as PDF
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=file_path, Quality:=xlQualityStandard _
            , IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
            True
End Sub

Answer 3

, :

If WorksheetFunction.CountIf(ActiveSheet.Range("A53:R56"), "*ProductX*") = 0 Then Exit Sub

:

If WorksheetFunction.CountIf(Range("A53:R56"), "*ProductX*") = 0 Then Exit Sub

ActiveSheet worksheet

Answer 4

, , 1 . , , . :

Dim test As Variant
Product = "ProductX"
' Set search_range as desired search range
search_range = Application.WorksheetFunction.Transpose(Sheets(1).Range("A53:A56"))
If UBound(Filter(search_range, Product)) > -1 Then
    file_path = Some_path_A
Else
    file_path = Some_path_B
End If

, . ,