在函数中，1表示Col"1〃；2表示Col"；2〃；。
当该函数没有找到从CCD_ 1到Col"的任何相关字符串时；1〃；它停止了。
我试图添加一个条件，即如果Col"；1〃；字符串与CCD_ 2字符串不匹配；"永久"；在"栏"中；1〃；因此函数将用于"；"永久"；以及CCD_ 3。
若字符串匹配，那个么函数就会工作。
原始功能
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
试试这个：
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