OilEconomist
Active Member
- Joined
- Dec 26, 2016
- Messages
- 439
- Office Version
- 2019
- Platform
- Windows
Thanks in advance. I do understand that there are other ways to look up values like with the VLOOKUP and MATCH, but I am using the following for an unspecified reason.
I am attempting to write a function which will find a corresponding value in another column in a given sheet, but I get the error "Run-time error '1004' Method 'Range' of object'_Global' failed" on this line of the function.
I think the issue is how I am setting up the search and find ranges. When I change the aforementioned line to the following I still get the same error.
The following is the macro sub code
The following is the function.
I am attempting to write a function which will find a corresponding value in another column in a given sheet, but I get the error "Run-time error '1004' Method 'Range' of object'_Global' failed" on this line of the function.
VBA Code:
Set Srch_Rng = Range(CLS & RS, Range(CLS).End(xlDown))
I think the issue is how I am setting up the search and find ranges. When I change the aforementioned line to the following I still get the same error.
VBA Code:
Set Srch_Rng = FndSht.Range(CLS & RS, FndSht.Range(CLS).End(xlDown))
The following is the macro sub code
VBA Code:
Sub Test()
'_________________________________________________________________________________________________
'Turn off alerts, screen updates, and automatic calculation
'Turn off Display Alerts
Application.DisplayAlerts = False
'Turn off Screen Update
Application.ScreenUpdating = False
'Turn off Automatic Calculations
Application.Calculation = xlManual
'_________________________________________________________________________________________________
'Dimensioning
Dim FndValue As String
Dim FndMtchVal As String
Dim ShtName As String
Dim RowStart As Long
Dim WhlPrt As Long
Dim ColNumSrch As Long
Dim ColNumFnd As Long
'_________________________________________________________________________________________________
'Code
FndMtchVal = "Dog"
ShtName = "Sheet1"
WhlPrt = 1
RowStart = 6
ColNumSrch = 1
ColNumFnd = 3
FndValue = FndMtchValF(FndMtchVal, ShtName, WhlPrt, RowStart, ColNumSrch, ColNumFnd)
MsgBox FndValue
'_________________________________________________________________________________________________
'Turn on alerts, screen updates, and calculate
'Turn On Display Alerts
Application.DisplayAlerts = True
'Turn on Screen Update
Application.ScreenUpdating = True
'Turn off Automatic Calculations
Application.Calculation = xlAutomatic
End Sub
The following is the function.
VBA Code:
Function FndMtchValF(FndMtchVal As String, ShtName As String, WhlPrt As Long, RowStart As Long, ColNumSrch As Long, ColNumFnd As Long) As String
'Dimesioning
Dim RS As Long
Dim OSV As Long
Dim CLS As String
Dim LookAtVal As String
Dim FndSht As Worksheet
Dim Srch_Rng As Range
Dim Fnd_Rng As Range
Dim 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 letters
CLS = Split(Cells(1, ColNumSrch).Address, "$")(1)
'Finding the Offset value
OSV = ColNumFnd - ColNumSrch
'For xlWhole or xlPart
If WhlPrt = 1 Then
LookAtVal = "xlWhole"
Else
LookAtVal = "xlPart"
End If
'Setting ranges to search and find
Set Srch_Rng = Range(CLS & RS, Range(CLS).End(xlDown))
Set Fnd_Rng = Srch_Rng.Find(What:=FndMtchVal, LookIn:=xlValues, LookAt:=LookAtVal)
'Code
With FndSht
With Fnd_Rng
If Fnd_Rng Is Nothing Then
FndMtchValF = "Lookup value does not exist in this sheet."
ElseIf Find_Range.Offset(, OSV).Value = "" Then
FndMtchValF = "Value is blank (no entry)."
Else
FndMtchValF = Find_Range.Offset(, OSV).Value
End If
End With
End With
End Function