Function to Find and Retrieve Value from A Sheet

OilEconomist

Active Member
Joined
Dec 26, 2016
Messages
439
Office Version
  1. 2019
Platform
  1. 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.
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
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Try it like
VBA Code:
        Set Srch_Rng = Range(CLS & RS, Cells(RS, CLS).End(xlDown))
you will also need to change this part
VBA Code:
       If WhlPrt = 1 Then
           LookAtVal = 1
        Else
            LookAtVal = 2
        End If
 
Upvote 0
Solution
Try it like
VBA Code:
        Set Srch_Rng = Range(CLS & RS, Cells(RS, CLS).End(xlDown))
you will also need to change this part
VBA Code:
       If WhlPrt = 1 Then
           LookAtVal = 1
        Else
            LookAtVal = 2
        End If

Thanks @Fluff ! So I searched the internet and it looks like for xlWhole you can use 1 and for xlPart you can use 2. I assume that's why told me to make the change?

Well your recommendations worked. Once again, thanks so much. I made some changes as I made some typos so I will paste it in a new section.
 
Upvote 0
Thanks so much to @Fluff, for not only the quick reply, but the solution. Here is the final solution so anyone who wants to use it, doesn't need to hassle with putting it together from the thread.

VBA code for the Sub:
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 - these are hardcoded, but you can read them in my a sheet/tab
       
        FndMtchVal = "Dog"
        ShtName = "Sheet1"
        WhlPrt = 1 '(1 for xlWhole or 2 for xlPart)
        RowStart = 6
        ColNumSrch = 1
        ColNumFnd = 3
       
        FndValue = FndMtchValF(FndMtchVal, ShtName, WhlPrt, RowStart, ColNumSrch, ColNumFnd)
       
        MsgBox FndValue 'you can also have these input back into a sheet.

       
    '_________________________________________________________________________________________________
    '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
            Calculate

   
End Sub

VBA Code for 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 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
        Set Srch_Rng = Range(CLS & RS, Cells(RS, CLS).End(xlDown)) 'will search from the start row to the last row of data. It finds the last row number
        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 = "Lookup value does not exist in this sheet."

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

End Function
 
Upvote 0
Glad to help & thanks for the feedback.
 
Upvote 0
Glad to help & thanks for the feedback.
@Fluff Thanks once again. I have a question as I thought I had set up the function to where if "Shee1" was not active (i.e. I was in another sheet/active e.g. Sheets("Sheet2).Activate, it would return the value, but I have to be in Sheet1 or have it activated. How do I modify the function where "Sheet1" does not have to be active?
 
Upvote 0
How about
VBA Code:
Set Srch_Rng = FndSht.Range(CLS & RS, FndSht.Cells(RS, CLS).End(xlDown))
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0
The following is the amended code from Post 4. That post works, but this one allows the Find to occur without having to activate the sheet. Thanks to @Fluff for assistance!


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 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
        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
        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 = "Lookup value does not exist in this sheet."

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

End Function
 
Upvote 0

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

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