Repeated search on the same worksheet

[Jens Lenge]

Hi folks,

I have trouble doing repeated searches on the same worksheet. I use this
small function to find a specific value on a given worksheet:

Function Find(ByRef sheet As Worksheet, ByVal label As String) As String
Dim cell As Range
Set cell = sheet.Cells.Find(label);
If cell Is Nothing Then
Find = "(not found)"
Exit Function
End If
cell = cell.Cells(1, 2) ' one cell to the right
Find = cell.Value
End Function

I want to have the search on the entire worksheet each time the function
is called. In particular, when I call it twice with the same "sheet" and
"label" arguments, I want it to find exactly the same occurence.

However, the function does only return the first (and only) occurence of
"label" when it is called for the first time. A second call fails and
delivers "(not found)".

What the heck am I doing wrong?

Jens

 

[Keith R]

I'll let one of the group experts give you a more definitive answer, but in
the meantime this might give you a place to start;

Set cell = sheet.Cells.Find(label);

sets your "cell" range to a specific cell where you found the first match

cell = cell.Cells(1, 2) ' one cell to the right

may set your range to a specific cell that doesn't include your match

I'd add a line at the top of the procedure to reset the cell range to the
entire sheet, or to prevent duplicate matches, the entire sheet below the
area of the last match.

I'd also avoid using the word "cell" as a variable (maybe "mycell" or
"targetcell" would work), but that's just me.

HTH,
Keith

 

[Jens Lenge]

sets your "cell" range to a specific cell where you found the first match

Yes, this should find the first match of "label" on the sheet.

may set your range to a specific cell that doesn't include your match

This is intended. The function looks for the first occurrence of the
"label" argument, and then returns the value of the cell to the right of it.

The point is that I do not want to *prevent* duplicate matches when the
function is called more than once. Instead, I want to make it return the
exact same result each time it is called with the same arguments.

And, just by chance, I have come across the solution: If I change the
line "cell = cell.Cells(1, 2)" to "Set cell = cell.Cells(1, 2)", then it
works. Don't ask me why.

Jens