May 30 2021 11:52 PM
May 30 2021 11:52 PM
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.
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
May 31 2021 08:13 AM
May 31 2021 08:59 AM
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.
May 31 2021 12:02 PM
Jun 01 2021 01:12 AMSolution