vlookup Dilemna

  • Thread starter Thread starter Greg85374
  • Start date Start date
G

Greg85374

Hi guys...Ive a question that has perplexed me for awhile now...

I have a range "H1:H100" (column H); in that column there is a cel
with a value of "1" stored. I want to know how to look in Column H an
locate and select the ROW where the value of "1" is located.

The trick is I must store the row where the value of 1 was found in
string so that I can call on it later in the program...Thanks i
advance youve already been really helpfull
 
Greg,

=MATCH(1,H1:H100,0) gives the row number if your range actually starts with
H1.

If not : suppose it starts at H5 : =MATCH(1,H5:H100,0) + Row(H5) - 1

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *
 
-----Original Message-----
Hi guys...Ive a question that has perplexed me for awhile now...

I have a range "H1:H100" (column H); in that column there is a cell
with a value of "1" stored. I want to know how to look in Column H and
locate and select the ROW where the value of "1" is located.

The trick is I must store the row where the value of 1 was found in a
string so that I can call on it later in the program...Thanks in
advance youve already been really helpfull.
Click to expand...

Greg


Try this

Sub FindAddress()
Dim rng As Range
Dim i As Long, nr As Long
Dim x As String
nr = Application.WorksheetFunction.CountA(Range("A:A"))
Set rng = Range(Cells(1, 8), Cells(nr, 8))
rng.Select
rng.Find(What:="1", After:=ActiveCell, LookIn:=xlValues,
LookAt:= _
xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext, MatchCase:=False) _
.Activate
x = ActiveCell.Address
MsgBox "The cell Address is " & x

End Sub

Regards
Peter
 
Back
Top