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

OilEconomist

Active Member
Joined
Dec 26, 2016
Messages
439
Office Version
  1. 2019
Platform
  1. Windows
Thanks in advance for any assistance anyone provides.

Why am I getting a "Code execution has been interrupted after the line
VBA Code:
NickName = FndMtchValF(aName, ShtNmSrc, 2, 2, 2, 1)

"Sheet 3"
the data sheets are as follows:
Data.xlsm
AB
1NicknameName
2James
3Earvin
4Kareem
5Michael
6Olajuwon
7Glenn
8Erving
9
10
11
12
13
14
15
16
17
18
19
Sheet3


"Sheet4"
Data.xlsm
AB
1Nick NameName
2Big Game JamesJames Worthy
3BuckEarvin Johnson
4CapKareem Abdul-Jabbar
5CoopMichael Cooper
6DreamHakeem Olajuwon
7DocGlenn Rivers
8Dr. JJulius Erving
9
10
11
12
13
14
15
16
17
18
19
Sheet4



VBA Code:
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
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
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.
 
Upvote 0
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 ?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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..
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,333
Members
452,636
Latest member
laura12345

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top