OilEconomist
Active Member
- Joined
- Dec 26, 2016
- Messages
- 439
- Office Version
- 2019
- Platform
- Windows
Thanks in advance for any assistance anyone provides.
Why am I getting a "Code execution has been interrupted after the line
"Sheet 3"
the data sheets are as follows:
"Sheet4"
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 | ||||
---|---|---|---|---|
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 |
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