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?

 

[tusharm]

Another thing to consider -- and it was only implicit in my previous post.

When writing a function, you must ensure it is general in nature. Since you have no idea what you were given by the consumer of your function, returning a (local) address of a range is a no-no. It is best to return the object returned by the Find method.

This way your function will return the correct result when passed any range object from any sheet from any workbook!

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:

{snip}
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.

 

[miconian]

Wow, tusharm. Thank you so much for the clear and verbose explanation. I think I really get it now... or at least, I'm a lot further along than I was. Your code works, too.

This problem is actually part of a bigger project that I'm putting together, so I'm sure I'll have more questions for you, but will save those for another thread.

 

[tusharm]

Glad to be of help.

Wow, tusharm. Thank you so much for the clear and verbose explanation. I think I really get it now... or at least, I'm a lot further along than I was. Your code works, too.

This problem is actually part of a bigger project that I'm putting together, so I'm sure I'll have more questions for you, but will save those for another thread.

 

[miconian]

Actually, I'm back with a related question. Maybe better to keep it in the same thread since it uses the function we've just discussed.

The bigger idea here is to use the function above to find the locations of five values, and then to use those locations as reference points for other actions. Basically, those values are headers that designate chunks of data on similar sheets, but the chunks are not always in the same place.

So my next step is to use the function to pull five locations, which I am gathering into an array. Perhaps I'm doing this successfully. But I can't be sure, because I'm failing in my efforts to use MsgBox to display the results. I have a feeling that this has something to do with my still-limited understanding of the Array object...

The following returns an error for 'object variable or with block variable not set.'

Sub Find_quarterly_markers()

' Create an array with five elements
' Each element will hold the cell address of a heading:
' Full Year, Q1, Q2, Q3, or Q4
' We will then use those locations as reference points to do stuff with the surrounding data
Dim arrHeaders(4) As Range


' We know that all the headings we're looking for can be found in column B
' So we'll create a range consisting of column B to use for the search
Dim rngSearch As Range
Set rngSearch = Sheets("Sheet1").Range("B:B")

' Make the location of Full Year the first element of the array
Set arrHeaders(0) = Find_Value_Location("Full Year", rngSearch)

' Make the location of Q1 the next element of the array
Set arrHeaders(1) = Find_Value_Location("Q1", rngSearch)

' Make the location of Q2 the next element of the array
Set arrHeaders(2) = Find_Value_Location("Q2", rngSearch)

' Make the location of Q3 the next element of the array
Set arrHeaders(3) = Find_Value_Location("Q3", rngSearch)

' Make the location of Q4 the next element of the array
Set arrHeaders(4) = Find_Value_Location("Q4", rngSearch)


Dim rngTest As Range
Set rngTest = arrHeaders(0)

MsgBox rngTest.Cells.Address

End Sub

 

[Norie]

Does the function actually return a range each time it's used?

It will only do that if the Find is succesful, otherwise it will return Nothing.

 

[miconian]

Norie: You're right! Actually, the workbooks that I'm planning to use this on will ALWAYS have the appropriate data, i.e. the find will always be successful. But in the special workbook I'm using to test the code, I had put the values to find in the wrong place! I moved them into column B, and now my test works.

An interesting thought I had. I actually only need the locations that get returned in the above test. I don't need the whole range object. So maybe I should actually be filling my array with cell addresses like $B$12 (as strings) instead of ranges. Because I'm going to be passing that data into another sub that will use those addresses further. Thoughts?

 

[Norie]

Why don't you need the objects?

Tusharm's post explained that it would be better to work with the range objects than the addresses.

For example if you did return $B$12 which worksheet and workbook would that be in?

 

[miconian]

I actually didn't see Tusharm's last comment until just now. I guess I understand, although my explanation may have made it sound like the function will be used for a variety of things, when really it will be used for the exact same thing in a variety of workbooks.

It's difficult for me to describe the big picture of what I'm trying to do all at once, so it's hard to ask for advice about how to do it best.

I think I will start another thread to discuss another facet of the project.

 

[tusharm]

Good development techniques are good development techniques. The development of a good generalized function costs the same (or less) than developing a custom function that works for the "exact same thing in a variety of workbooks" -- and it will work in any number of other instances saving you time in the future.

So, why build a sub-optimal function?

I actually didn't see Tusharm's last comment until just now. I guess I understand, although my explanation may have made it sound like the function will be used for a variety of things, when really it will be used for the exact same thing in a variety of workbooks.

It's difficult for me to describe the big picture of what I'm trying to do all at once, so it's hard to ask for advice about how to do it best.

I think I will start another thread to discuss another facet of the project.

 

[miconian]

tusharm, I'm sure you're right, and I'm fine with doing it the way you suggest, because I want to do things properly. That said, I'm afraid that I don't really understand the necessity of ranges as return values here, if I'm only going to extract the locations later on.

I have started a related thread that I hope you'll look at:

http://www.mrexcel.com/forum/excel-questions/657035-programmatic-vlookups.html

I hope the gods of mrexcel don't judge me too harshly for starting another thread, but it really is (from my point of view) a different fundamental question. However, for those of you who read this thread, if you're still interested, it will provide more insight into why I posed this question and what I'm trying to do overall. Thanks for your time.