Please evaluate my new function

C

Caezar

I am sure somebody already came up with a solution to do a vlookup
towards the left, but I wanted to try and build my very own function,
which I dubbed VSearch. This is the first function I design and I seek
your opinion on the code, as well as suggestions to improve on it.

The function requires three arguments, just like vlookup:
Var_search represents what is looked up (either a value or the
reference to a cell)
Rng_where represents the range where the value defined above will be
searched
Int_column_offset represents the number of columns between the range
Rng_where and the range that contains the value to be returned by
the function.

Now here is the code. What do you guys think?



Function VSearch(Var_search_what As Variant, Rng_where As Range,
Int_column_offset As Integer) As Variant

Dim Int_row_position As Integer
Dim Rng As Range
Dim Bln_Record_Found As Boolean
Dim Var_Searched_Value As Variant

'Checks whether the user input a value or a reference to another cell
If VarType(Var_search_what) = 0 Then
Var_Searched_Value = Range(Var_search_what).Value
Else: Var_Searched_Value = Var_search_what
End If

'Find location of matching record in target range
Bln_Record_Found = False

For Each Rng In Rng_where
If Rng.Value = Var_Searched_Value Then
Int_row_position = Rng.Row - Rng_where.Cells(1, 1).Row + 1
Bln_Record_Found = True
End If
If Bln_Record_Found = True Then Exit For
Next


If Bln_Record_Found = True Then
VSearch = Rng_where.Offset(0,
Int_column_offset).Cells(Int_row_position, 1).Value
Else: VSearch = "Not found"
End If

End Function
 
B

Bob Phillips

Why not use Find to check if value exists rather than looping?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
B

Bob Phillips

It's described well in VBA help


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
Top