时间戳

首页 > 解决方案 > 添加“如果未找到匹配项”条件以运行

excel - 添加“如果未找到匹配项”条件以运行

问题描述

在函数中，1 代表 Col“1”，2 代表 Col“2”。

当此函数未找到从cater到 Col"1" 的任何相关字符串时，它会停止。

我正在尝试添加一个条件，即如果 Col"1" 字符串与cater字符串不匹配，则 Col"1" 中存在名称为“Permanent”的字符串，因此该函数将与Condit, 2.

如果字符串匹配，则函数将起作用。

原始功能

Public Function GetRowNo_ByCaterAndCondit(Cater As String, Condit As String) As Long
    
    GetRowNo_ByCaterAndCondit = usedfunctions.GetRowNoTwoColumns( _
        ThisWorkbook.Sheets("Sheet1"), Cater, 1, Condit, 2)
        
End Function

我试过这个。

Public Function GetRowNo_ByCaterAndCondit(Permanent as string, Cater As String, Condit As String) As Long
    
    If GetRowNo_ByCaterAndCondit = usedfunctions.GetRowNoTwoColumns( _
        ThisWorkbook.Sheets("Sheet1"), Cater, 1, Condit, 2)

    'if not macthed these Cater, 1 then

    GetRowNo_ByCaterAndCondit = usedfunctions.GetRowNoTwoColumns( _
        ThisWorkbook.Sheets("Sheet1"), permanent, 1, Condit, 2)
    End if
    
End Function

这是GetRowNoSearchTwoColumns功能：

Public Function GetRowNoSearchTwoColumns(Sht As Worksheet, _
    StringToFind1 As String, ColumnNumber1 As Integer, _
    StringToFind2 As String, ColumnNumber2 As Integer) As Long
    
    On Error GoTo GetRowNoSearchTwoColumns_CleanUp_ErrorCheck

    Dim OldReferenceStyle As XlReferenceStyle
    Dim SheetUsedRange As Range
    Dim LastRow As Long
    Dim LastCol As Integer
    Dim CompleteRange As Range
    Dim StrFormula As String
    
    OldReferenceStyle = Application.ReferenceStyle
    Application.ReferenceStyle = xlR1C1
    
    ' Avoid problems if first row is empty, or first N rows (same problem with columns too)
    Set SheetUsedRange = Sht.UsedRange
    LastRow = SheetUsedRange.Row + SheetUsedRange.Rows.Count - 1
    LastCol = SheetUsedRange.Column + SheetUsedRange.Columns.Count - 1
    Set CompleteRange = Sht.Range(Sht.Cells(1, 1), Sht.Cells(LastRow, LastCol))
    
    StrFormula = "=MATCH(""" & StringToFind1 & """&""" & StringToFind2 & """," & _
        "'" & Sht.Name & "'!" & CompleteRange.Columns(ColumnNumber1).Address(ReferenceStyle:=xlR1C1) & "&" & _
        "'" & Sht.Name & "'!" & CompleteRange.Columns(ColumnNumber2).Address(ReferenceStyle:=xlR1C1) & ",0)"
    
    GetRowNoSearchTwoColumns = Application.Evaluate(StrFormula)
    

GetRowNoSearchTwoColumns_CleanUp_ErrorCheck:
    ' CleanUp
    Application.ReferenceStyle = OldReferenceStyle
    Set SheetUsedRange = Nothing
    Set CompleteRange = Nothing
    
    If Err.Number <> 0 Then
        GetRowNoSearchTwoColumns = 0
    End If
End Function

标签： excelvba

解决方案

试试这个：

Public Function GetRowNo_ByCaterAndCondit(Permanent As String, Cater As String, _
                                                               Condit As String) As Long
    Dim res, ws As Worksheet
    
    Set ws = ThisWorkbook.Sheets("Sheet1")
    
    res = usedfunctions.GetRowNoSearchTwoColumns(ws, Cater, 1, Condit, 2)
    
    'if no match `res` will be zero - try again with `Permanent`
    If res = 0 Then
        res = usedfunctions.GetRowNoSearchTwoColumns(ws, Permanent, 1, Condit, 2)
    End If

    GetRowNo_ByCaterAndCondit = res
End Function

'Match two values in specific columns on a sheet and return the row number
'  Return zero if no match
Public Function GetRowNoSearchTwoColumns(Sht As Worksheet, _
            StringToFind1 As String, ColumnNumber1 As Long, _
            StringToFind2 As String, ColumnNumber2 As Long) As Long
    
    Dim f As String, res, lrow As Long
    
    'get last-used row for each column and use the max value
    lrow = Application.Max(Sht.Cells(Rows.Count, ColumnNumber1).End(xlUp).Row, _
                           Sht.Cells(Rows.Count, ColumnNumber2).End(xlUp).Row)
    
    f = "=MATCH(""<v1>""&""<v2>"",<addr1>&<addr2>,0)"
    f = Replace(f, "<v1>", StringToFind1)
    f = Replace(f, "<v2>", StringToFind2)
    f = Replace(f, "<addr1>", Sht.Cells(1, ColumnNumber1).Resize(lrow).Address(0, 0))
    f = Replace(f, "<addr2>", Sht.Cells(1, ColumnNumber2).Resize(lrow).Address(0, 0))
    
    Debug.Print f
    
    res = Sht.Evaluate(f) 'Use the WorkSheet.Evaluate form (cleaner as you don't need
                          ' to include the sheet name.  Also no need to use R1C1 here.
    
    GetRowNoSearchTwoColumns = IIf(IsError(res), 0, CLng(res)) 'return zero if no match
    
End Function

推荐阅读