Adding condition to existing function

[ShazSh]

I hope to get some help regarding the function from experts.

In the below function 1 is represent to Col"1" and 2 is represent to Col"2"

I have been using this function and its working fine but when it does not find any relevant strings from cater to Col"1" then it stops.

I am trying to add one condition in below function that is if Col"1" strings are not matched with cater strings then there are strings with the name of "Permanent" in the Col"1" so the function will go for the "Permanent" along with the Condit, 2.

If string matches then same below function will work.

Original Function

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

I would appreciate it if you could help me with this. Thanks

I am trying but its not seems to be working.

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

Here is the GetRowNoTwoColumns function

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

[#2]

Why would you want to do this using a slow UDF, rather than placing the formula in question directly in the cell?

[#3]

@ShazSh 

I would second the question asked by @Jan Karel Pieterse : why are you writing a routine to set a user defined function. The great likelihood is that the same result could be achieved--more quickly and reliably--just be using built-in functions.

My guess is that you have a programming background and therefore find writing your own routines satisfying. In reviewing the posts you've started here at techcommunity I do note that the majority (if not the totality) concern VBA or macro questions. It's perfectly legitimate to write routines, of course.

Back in the 1990s I inherited responsibility for a monthly report that went to the top management of my corporation, a report that summarized employee headcounts by division, location, payclass, etc. When I inherited it, it was being produced (in Lotus 1-2-3) by means of an elaborate Basic program based on an SQL extraction of data. I took the raw data extract and used built-in functions in the spreadsheet to produce the same report. Much faster--virtually instantaneous, compared to 20 to 30 minutes. And every bit as reliable. The IT person who had created the former method had not done anything wrong; it just was  a mindset that came in to the task thinking "this is such a big task that it surely requires a program to be written." I came into it thinking, "this is just a task summarizing data, and spreadsheets are made for that," coupled with the fact that I had, in those days, a lot of fun reading the (printed) user manuals and discovering the wealth of power that was present in the built-in functions.

That power has extended a LOT in the intervening 25 years--Excel contains an incredible array of functions--so my default of seeking to accomplish tasks without macros/VBA is still the approach I recommend. Will I ever write (or record) a short VBA routine to accomplish a repetitive task? Yes. But it's not my default.

Whether this has any relevance to your situation is up to you....I just wanted to add it as reinforcement to Jan's question.

[#4]

Yes i know thins can be achieve directly through excel formula but this functions has been created by developer who was working for the organisation before me but i want to change these functions to single code or two code solution.

Because i am unable to understand his writings i really do not know why he created separate functions.

I just need to add IF condition to as i stated above.

Formula cannot be used because sheet is already 40MB no single formula is used. File has just codes, functions, classes and user-forms.

[#5 · Accepted solution]

The normal formula you would need is something like:
=MATCH(A1&B1&C1,Sheet1!$A$2:$A$1000&Sheet1!$B$2:$B$1000&Sheet1!$C$2:$C$1000,0)
Where the three conditions are in A1, B1 and C1 and the columns to look them up are columns A, B and C on Sheet1, rows 2 to 1000.
Your argument that a formula cannot be used sounds strange. A 40 MB workbook isn't that special if most of it is just plain data.