Isn't that typical. I first created it with column, then saw that you had
said row.

You want

Set oCell = .Columns(4).Find(cellValue)


--

HTH

Bob Phillips

"Reno" wrote in message
...
Shoot, Bob, I meant to say column!!

Maybe this?

Set oCell = .Cols(4).Find(cellValue) ???

But anyway, I'll give it a shot as is and see what happens...

Once again, thanks!!

Reno


"Bob Phillips" wrote in message
...
Reno,

You are using the VBA Find method, so that's a big plus, it's not a cell
by
cell search. To limit it though, use

Set oCell = .Rows(4).Find(cellValue)
instead of
Set oCell = .Cells.Find(cellValue)

and it wil be limited to the 4th row.

--

HTH

Bob Phillips

"Reno" wrote in message
...
Bob! THANKS!!! It works like a charm!!

May I ask you something though. These spreadsheets are huge! Will that
impact the search time (searching by cell)? Do you think I could limit
it
somehow to the row that the ID is in on sheet 2?

Know what I mean?

Once again, THANK YOU!!!

Reno


"Bob Phillips" wrote in message
...
Reno,

Try this

Sub current_Cel()
Dim cellValue
Dim oCell As Range

cellValue = ActiveCell.Value

With Worksheets("Sheet2")
.Activate
Set oCell = .Cells.Find(cellValue)
If Not oCell Is Nothing Then
MsgBox "Got it"
oCell.Select
End If
End With

End Sub


--

HTH

Bob Phillips

"Reno" wrote in message
...
Folks:

Been staring at this code for days, and am frankly lost. Not
really
an
Excel
programmer but cobbled this together from samples and research.
All
I
wish
to do is to take a value from one sheet and position to that same
value
in
another sheet, and then set the row in the sheet to current row.
Here
is
the
code....

Sub current_Cell()

MsgBox ActiveCell.Value

For Each RosRng In Sheets("sheet2").UsedRange.Rows
If RosRng.Range("D1").Value = ActiveCell.Value Then
MsgBox "gotit"
End If
Next

End Sub

I have 4 rows in the other sheet and it does loop through 4 times.
However,
there seems to be NOTHING in RosRng.Range("D1").Value. It will not
light
up
in the debugger either which leads me to believe that something
ain't
quite
right.

Can someone please shed some light on what I may be doing wrong.

Also, can someone tell me how to set current row. If I find the
above
value
in say, row 7, how can I postion to that row or even column.

Thanks a bunch!!

Reno