vba - 测试特定字符串范围的更短方法

Question

尝试创建将我的 Excel 发票表导出为 PDF 的代码，并导出到指定的文件路径。该路径基于发票是否列出特定产品 ProductX。

这是我想到的，但循环遍历范围内的每个单元格来查看 ProductX 是否存在似乎很麻烦。

有更简单的方法吗？感谢任何帮助!

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

您可以使用Find进行部分匹配。

此代码假设返回的路径包含您需要的filepath变量 - 您可能需要对此进行调整。

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