Hello,
I used the =XMATCH() function in the worksheet to solve the problem and it worked fine. When I wanted to use the function in the VBA code to automate the task, it keeps reporting an error at runtime, see image. What is wrong in the VBA code?
How I wrote the VBA code can be seen in this sample (the original is written in the project in Module1):
Function Zanes(ByVal Vstup As String, ByVal Lst As String, ByVal Slp as Long) As Boolean
Dim Rdmax As Long, RnZdroj As Range, Nález As Variant
Worksheets(Lst).Activate ' sheet for writing down phrases
' the last line already filled in the phrase list
Rdmax = Cells(Rows.Count, Slp).End(xlUp).Row
' definition of the range in which the XMATCH function will search
Set RnZdroj = ActiveSheet.Range("B1:B" & Rdmax)
Nález = WorksheetFunction.XMatch(Vstup, RnZdroj, 0) ' the program is interrupted here
If IsError(Nález) Then ' XMATCH success test
Cells(Rdmax + 1, Slp) = Vstup ' the first entry of the phrase in the list
Cells(Rdmax + 1, Slp - 1) = 1 ' default value of the counter
Else
' if a phrase is repeated in the list, the counter will be incremented
Cells(Nález, Slp - 1) = Cells(Nález, Slp - 1) + 1
End If
Zanes = True
Set RnZdroj = Nothing
Worksheets(LstZd).Activate ' return to the DataSheet (and LstZd is a global variable)
End Function
Note: Even after specifying function calls such as:
Nález= Application.WorksheetFunction.XMatch(Input, RnSource, 0)
the error was not resolved.
I am using MS EXCEL 365, on operating system:
Processor Intel(R) Core(TM) i5-6500T CPU @ 2.50GHz
Installed RAM 16.0 GB (usable: 15.9 GB)
System type 64-bit operating system, x64 platform processor
Pen and Touch Control Pen input and touch input are not available for this display.
Windows 10 Pro edition
Version 22H2
Installation date 17.01.2021
Operating System Build 19045.2728
Performance Windows Feature Experience Pack 120.2212.4190.0
Thank you for your reply. Yours sincerely
Stanislav