Hey
I need to get a value from a cell and check wheter it exists in a range.
Right now I have:
If Not IsError(Application.Match(Cells(1, 2).Value, Range("A1:A10"), 0)) Then
...
This doesn't work as I wanted. Also, I think it is very slow.
I'd like to achieve something in the way:
If Value_In_Range(Range("B1").Value, Range("A1:A10")) Then
...
Where the function would be something like:
Function Value_In_Range(Range1 As Range, Range2 As Range) As Boolean
With Range2
Set Rng = .Find(What:=Range1.Text, _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
End With
InRange = Not Rng Is Nothing
Set Rng = Nothing
End Function
Now, I am very new to VBA, so my questions are:
- Can I have a Range.find function which returns True if a match occurs?
Why do I want that? Well, looping through the cells is very slow (I'd like the code to be as fast as possible) and using Application.Functions doesn't seem right to me, especially because I read that they aren't fast either.
Thx
Bookmarks