VBA Code Find Function gives error "Code execution has been interrupted"

[OilEconomist]

Thanks in advance for any assistance anyone provides.

Why am I getting a "Code execution has been interrupted after the line

NickName = FndMtchValF(aName, ShtNmSrc, 2, 2, 2, 1)

"Sheet 3"
the data sheets are as follows:

Data.xlsm
	A	B
1	Nickname	Name
2		James
3		Earvin
4		Kareem
5		Michael
6		Olajuwon
7		Glenn
8		Erving
9
10
11
12
13
14
15
16
17
18
19
Sheet3

"Sheet4"

Data.xlsm
	A	B
1	Nick Name	Name
2	Big Game James	James Worthy
3	Buck	Earvin Johnson
4	Cap	Kareem Abdul-Jabbar
5	Coop	Michael Cooper
6	Dream	Hakeem Olajuwon
7	Doc	Glenn Rivers
8	Dr. J	Julius Erving
9
10
11
12
13
14
15
16
17
18
19
Sheet4

Sub FndNm()

Dim RowNo As Long

Dim NickName As String
Dim aName As String
Dim ShtNmUdt As String
Dim ShtNmSrc As String

Dim aCell As Range
Dim Rng As Range


ShtNmUdt = "Sheet3"
ShtNmSrc = "Sheet4"

'FndMtchValF(FndMtchVal As String, ShtName As String, WhlPrt As Long, RowStart As Long, ColNumSrch As Long, ColNumFnd As Long) As String

Sheets(ShtNmUdt).Activate

Set Rng = Sheets(ShtNmUdt).Range("B2:B8")

For Each aCell In Rng
    aName = Sheets(ShtNmUdt).Cells(aCell.Row, aCell.Column)
    RowNo = aCell.Row
    NickName = FndMtchValF(aName, ShtNmSrc, 2, 2, 2, 1)
    Sheets(ShtNmUdt).Cells(RowNo, 1) = NickName
Next


End Sub




'*********************************************************************************************************
'This function will look up a value on another sheet with use of the find function and return the
    'corresponding value on the same row in another column

Function FndMtchValF(FndMtchVal As String, ShtName As String, WhlPrt As Long, RowStart As Long, ColNumSrch As Long, ColNumFnd As Long) As String
 EnableCancelKey = xlDisabled
   
   'These are the input values for the function
        'FndMtchVal (string) - the actual value to be searched
        'ShtName (string) - name of sheet to be searched
        'WhlPrt (long) - search for an exact value (1) or if the entry contains that word (2)
        'RowStart (long) - row to start search (avoids using any notes, the header row, etc above the search start point)
        'ColNumSrch - column number that contains the entry to search for the "FndMtchVal"
        'ColNumFnd - column number to retrive the data from


'_________________________________________________________________________________________________________________
 'Turn off alerts, screen updates, and automatic calculation
  Application.DisplayAlerts = False
  Application.ScreenUpdating = False
  Application.Calculation = xlManual


 '_________________________________________________________________________________________________________________
 'Code
 
 
    'Dimensioning
        Dim RS As Long
        Dim OSV As Long
       
        Dim CLS As String
       
        Dim FndSht As Worksheet
       
        Dim Srch_Rng As Range
        Dim Fnd_Rng As Range
               
       
    'Setting sheet to find value in
        Set FndSht = Sheets(ShtName)
        
    'Resetting the starting row "RowStart" to a shorter variable
        RS = RowStart
       
    'Finding the column letter
        CLS = Split(Cells(1, ColNumSrch).Address, "$")(1)
       
    'Finding the Offset value
        OSV = ColNumFnd - ColNumSrch
    
       
    'Setting ranges to search and find
        On Error Resume Next
        Set Srch_Rng = FndSht.Range(CLS & RS, FndSht.Cells(RS, CLS).End(xlDown)) 'will search from the start row to the last row of data. It finds the last row number
        On Error Resume Next
        Set Fnd_Rng = Srch_Rng.Find(What:=FndMtchVal, LookIn:=xlValues, LookAt:=WhlPrt)

      
    'Code - using "With" and "End With" avoids having to activate the sheet
        With FndSht
            With Fnd_Rng
   
                If Fnd_Rng Is Nothing Then 'it doesn't find the value you are looking for so there is no corresponding cell value in another column
                    FndMtchValF = "DNE"

                ElseIf Fnd_Rng.Offset(, OSV).Value = "" Then 'it finds the value, but the corresponding cell value in the other column is blank
                    FndMtchValF = "blank"
       
                Else
                    FndMtchValF = Fnd_Rng.Offset(, OSV).Value 'it finds the value
       
                End If
           
            End With
        End With
        
End Function

 

[Joe4]

Have you tried stepping into your code, and going through your code line-by-line (using the F8 key) to see if you can see what is happening, and where it seems to "choke"?
Note that while you are doing this, you can hover over any variable in your code to see what its value is at that point in time.

 

[Alex Blakenburg]

Seems to work fine on your sample data.
Do you have any event macros running ? If you do can you show us the macros.
If you don't do you have any add-ins running ? If so can you disable them and see if the problem goes away ?

 

[OilEconomist]

Have you tried stepping into your code, and going through your code line-by-line (using the F8 key) to see if you can see what is happening, and where it seems to "choke"?
Note that while you are doing this, you can hover over any variable in your code to see what its value is at that point in time.

Thanks @Joe4 for your response. Let me try this and get back with you. I think there is an issue with my MS Excel as I had a similar issue previously and when I closed everything down and came back the next day, I wasn't having the issue anymore.

 

[OilEconomist]

Seems to work fine on your sample data.
Do you have any event macros running ? If you do can you show us the macros.
If you don't do you have any add-ins running ? If so can you disable them and see if the problem goes away ?

Thanks @Alex Blakenburg for your response. I think there is an issue with my MS Excel as I had a similar issue previously and when I closed everything down and came back the next day, I wasn't having the issue anymore. I will test it again later and let you know.

 

[Alex Blakenburg]

Thanks for the update. Let us know how you go.

 

[HarryS]

Thank you folks
The Shut it down and restart WORKED..
Much better than an axe through the computer.
That must be somewhere near the 4327 th. time MrExcell has saved some of my sanity..