type mismatch on passing a range value

[miconian]

The idea here is that I can pass the function a value and a range, and then it searches that range for the value, and returns the address of the cell in which the value appears.

Function Find_Value_Location(FindString As String, Rng As Range)
        
    If Trim(FindString) <> "" Then
        With Sheets("Sheet1").Range(Rng)
            Set Rng = .Find(What:=FindString, _
                            After:=.Cells(.Cells.Count), _
                            LookIn:=xlValues, _
                            LookAt:=xlWhole, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False)
        End With
    End If
    
    Find_Value_Location = Rng.Cells.Address
    
End Function

But when I put this in the immediate window to test:

Call Find_Value_Location("Q1","B:B")

I get a type mismatch error. What gives?

 

[Gary's Student]

Call Find_Value_Location("Q1",Range("B:B"))

 

[miconian]

Hm, okay.. but then I get an "application-defined or object-defined error" on this line:

        With Sheets("Sheet1").Range(Rng)

 

[tusharm]

If you want to pass the range address, don't declare the parameter as a range. Instead use a string.

Function Find_Value_Location(FindString As String, Rng As String)

The idea here is that I can pass the function a value and a range, and then it searches that range for the value, and returns the address of the cell in which the value appears.

Function Find_Value_Location(FindString As String, Rng As Range)
        
    If Trim(FindString) <> "" Then
        With Sheets("Sheet1").Range(Rng)
            Set Rng = .Find(What:=FindString, _
                            After:=.Cells(.Cells.Count), _
                            LookIn:=xlValues, _
                            LookAt:=xlWhole, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False)
        End With
    End If
    
    Find_Value_Location = Rng.Cells.Address
    
End Function

But when I put this in the immediate window to test:

Call Find_Value_Location("Q1","B:B")

I get a type mismatch error. What gives?

 

[miconian]

I think it has to be an object for my search to work, though.

I did what you suggested, and used this as a test:

Call Find_Value_Location("Q1","B:B")

...and got an 'object required' error on this line:

   Set Rng = .Find(What:=FindString, _

 

[mirabeau]

The idea here is that I can pass the function a value and a range, and then it searches that range for the value, and returns the address of the cell in which the value appears.

Function Find_Value_Location(FindString As String, Rng As Range)
        
    If Trim(FindString) <> "" Then
        With Sheets("Sheet1").Cells   'Range(Rng)
            Set Rng = .Find(What:=FindString, _
                            After:=.Cells(.Cells.Count), _
                            LookIn:=xlValues, _
                            LookAt:=xlWhole, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False)
        End With
    End If
    
    Find_Value_Location = Rng.Address   'Rng.Cells.Address
    
End Function

...

With changes as in red, does that work for you?

 

[Gary's Student]

With Sheets("Sheet1").Range(Rng.Address)

 

[miconian]

Thanks guys, but I still get "type mismatch" errors in both cases.

 

[miconian]

Probably worth mentioning that my original code actually works when it's a subroutine instead of a function, and I'm designating the values within the code. This works, to search for the string "Q1" in column B:

Sub Find_Value_Location_Simple()
        
  Dim FindString As String
  Dim Rng As Range
  
  FindString = "Q1"
        
    If Trim(FindString) <> "" Then
        With Sheets("Sheet1").Range("B:B")
            Set Rng = .Find(What:=FindString, _
                            After:=.Cells(.Cells.Count), _
                            LookIn:=xlValues, _
                            LookAt:=xlWhole, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False)
        End With
    End If
    
    MsgBox Rng.Cells.Address
    
End Sub

But I'm going to be doing a lot of such searches, on different ranges and for different strings, so I wanted to turn it into a function to make the code more easily re-usable.

 

[tusharm]

You have the correct idea but not of what constitutes a range or how to use parameters, which in turn leads to an incorrect implementation.

"B:B" is a string. It is not a range. range("B:B") is.

When you declare a parameter, the argument you use must be of the same type.

So, if you have

function myFunc(Rng as range)

you can then call the function as

myFunc(Range("b:b"))

but *not*

myfunc("B:B")

You can also use

dim X as Range:set x=sheets("sheet1").range("b:B")
myFunc(X)

So, if your function declaration is

Function Find_Value_Location(FindString As String, Rng As Range)

you can call the function as

Find_Value_Location("abc", range("b:b"))

or

dim X as range:set x=sheets("sheet1").range("b:b")
Find_Value_Location("abc", X)

Next, in the function, you should not reuse the Rng variable..at least not until you have an advanced level knowledge of how arguments are passed to a function and how they are processed internally by VBA. In addition, you can return a range object rather than the local address of the required range.
So, use something like:

Function Find_Value_Location(FindString As String, Rng As Range)as range
        
    If Trim(FindString) <> "" Then
        With Rng
            Set Find_Value_Location = .Find(What:=FindString, _
                            After:=.Cells(.Cells.Count), _
                            LookIn:=xlValues, _
                            LookAt:=xlWhole, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False)
        End With
    End If

End Function

Do note that none of the code above has been tested.

You may want to see how I coded a "find all" routine:
FindAll
Find All

Probably worth mentioning that my original code actually works when it's a subroutine instead of a function, and I'm designating the values within the code. This works, to search for the string "Q1" in column B:

Sub Find_Value_Location_Simple()
        
  Dim FindString As String
  Dim Rng As Range
  
  FindString = "Q1"
        
    If Trim(FindString) <> "" Then
        With Sheets("Sheet1").Range("B:B")
            Set Rng = .Find(What:=FindString, _
                            After:=.Cells(.Cells.Count), _
                            LookIn:=xlValues, _
                            LookAt:=xlWhole, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False)
        End With
    End If
    
    MsgBox Rng.Cells.Address
    
End Sub

But I'm going to be doing a lot of such searches, on different ranges and for different strings, so I wanted to turn it into a function to make the code more easily re-usable.