Another version:

Option Explicit
Sub testme01()

Dim myRng As Range
Dim myCell As Range
Dim myInputRng As Range
Dim FoundCell As Range

Application.ScreenUpdating = False

'use the same name for consistency
Set myRng = Worksheets("rows").Range("myrng")

With Worksheets("audit")
'headers in row 1???
Set myInputRng = .Range("a2", .Cells(.Rows.Count, "A").End(xlUp))
End With

For Each myCell In myInputRng.Cells
Set FoundCell = myRng.Cells.Find(what:=myCell.Value, _
lookat:=xlWhole, LookIn:=xlValues, _
MatchCase:=False, searchorder:=xlByRows)

If FoundCell Is Nothing Then
myCell.Offset(0, 1).Value = "Not found"
Else
myCell.Offset(0, 1).Value = FoundCell.Column
End If
Next myCell

Application.ScreenUpdating = True
MsgBox "Done!"
End Sub

mjack003 wrote:

Hi,

I've pulled my hair out trying to nest functions and try various
different ways to do this but here's the problem. I have two
worksheets. The first sheet, "Rows" has a named range from A2:CV500
'myRng'. All cells within the range are either blank, or hold a unique
workorder number anywhere from 4 to 8 digits scattered randomly.
The second worksheet "Audit" contains the unique workorder
numbers from worksheet "Rows", listed in ascending order in Column A
without any spaces.

What I need to do is look up the number in column A on my "Audit"
Sheet, locate it on the "Rows" sheet and return the column # it was
located in.

Ex. Audit!A1 = 15899 , Row!C48 = 15899 so Audit!B1 = 3 since "15899"
was found in 'C48' on the "Row" worksheet.

Any help would be great!

Mjack

--
mjack003
------------------------------------------------------------------------
mjack003's Profile: http://www.excelforum.com/member.php...fo&userid=5141
View this thread: http://www.excelforum.com/showthread...hreadid=468422

--

Dave Peterson