测试某个字符串范围的较短方法

Trying to create code that will export my Excel invoice sheet to PDF, to a specified file path. The path is based on the whether the invoice lists a certain product, ProductX.

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

This is what I came up with, but seems cumbersome to loop through every single cell in a range to see if ProductX is there.

这就是我提出的，但是在一个范围内遍历每个单元格以查看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

4 个解决方案

#1

You can use Find for a partial match.

您可以使用“查找”进行部分匹配。

This code assumes that the returned path contains the filepath variable you need - you may need to tweak this.

此代码假定返回的路径包含您需要的文件路径变量 - 您可能需要调整它。

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

#2

Based on what brettdj has suggested, you can use the proposed code like below...

根据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

#3

I think the shortest way is the following:

我认为最简短的方法如下：

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

which can be further reduced to:

可以进一步减少到：

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

since ActiveSheet is the default ragnge worksheet qualification

因为ActiveSheet是默认的ragnge工作表资格

#4

There is a way to find exactly that with just a line of code however it will work only the if you search on 1 column. For your case I think it will work because usually the product name will be in a column. The code is as follows:

有一种方法可以通过一行代码找到确切的代码，但只有在您搜索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

You can give it a try and let me know if this works for you. If not I will try to find a way to do it with multiple columns and improve the answer

您可以尝试一下，如果这对您有用，请告诉我。如果不是，我将尝试找到一种方法来使用多列并改进答案

#1