value in the 1st col of a range

[banavas]

Dear Friends,

How can I check whether a value A exists in a range and specially i
the first column of the range?

I cannot figure out how does the rangeL.find() works.

msgbox(rangeL.Find(A)) breaks my code.

At last how can I pass a reference in a function?

ex.

VlookUp(ref, Range, 5, 0)

ref - the reference name of a value.

Thanks,

 

[Patrick Molloy]

You need to study what objects are. Look in Help for find.

Where you use Find as a range object method, then it
returns a range opject. You cannot use msgbox for a range
object....you need to specify WHAT property you
want...and in this cas eyou'd use Value.

DIM Result As Range
SET Result = Source.Find(What)
' source is the range that you want to search in
IF NOT Result is Nothing THEN
' Result is a range object that has the value that you
' searched for
msgbox "Found in " & Result.Address
END IF


Passing reference to functions is one of the most basic
things to learn. Read HELP on Functions and procedures.

Patrick Molloy
Microsoft Excel MVP

 

[Bob Phillips]

Dim rng As Range
Dim oCell As Range

Set rng = Range("A1:H100")
Set oCell = rng.Columns(1).Find("a")
If Not oCell Is Nothing Then
MsgBox oCell.Address
End If

and

Dim ref
Dim rng As Range

Set rng = Range("A1:H100")
ref = "a"
MsgBox Application.VLookup(ref, rng, 5, 0)


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)