Function to Find and Retrieve Value from A Sheet

[OilEconomist]

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.

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.

Set Srch_Rng = FndSht.Range(CLS & RS, FndSht.Range(CLS).End(xlDown))

The following is the macro sub 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.

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

 

[Fluff]

Try it like

        Set Srch_Rng = Range(CLS & RS, Cells(RS, CLS).End(xlDown))

you will also need to change this part

       If WhlPrt = 1 Then
           LookAtVal = 1
        Else
            LookAtVal = 2
        End If

 

[OilEconomist]

Try it like

        Set Srch_Rng = Range(CLS & RS, Cells(RS, CLS).End(xlDown))

you will also need to change this part

       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.

 

[OilEconomist]

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:

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:

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

 

[Fluff]

Glad to help & thanks for the feedback.

 

[OilEconomist]

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?

 

[Fluff]

How about

Set Srch_Rng = FndSht.Range(CLS & RS, FndSht.Cells(RS, CLS).End(xlDown))

 

[OilEconomist]

T

How about

Set Srch_Rng = FndSht.Range(CLS & RS, FndSht.Cells(RS, CLS).End(xlDown))

@Fluff Thanks. That works. I will repost the amended code.

 

[Fluff]

You're welcome & thanks for the feedback.

 

[OilEconomist]

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!

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