Return a column # from an array

M

mjack003

Hi,

I've pulled my hair out trying to nest functions and try variou
different ways to do this but here's the problem. I have tw
worksheets. The first sheet, "Rows" has a named range from A2:CV50
'myRng'. All cells within the range are either blank, or hold a uniqu
workorder number anywhere from 4 to 8 digits scattered randomly.
The second worksheet "Audit" contains the unique workorde
numbers from worksheet "Rows", listed in ascending order in Column
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 wa
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 :mad
 
M

mjack003

Type mismatch error on line "num = cell.value"

....fixed the mismatch error...now getting error "object variable no
set"

Figured out its erroring a number listed on the "audit" sheet can't b
found on the "Rows" sheet so where would I implement an If-else loop?
Would need to do a check to make sure it even exists on the "Rows
sheet
 
B

Biff

Hi!

One way:

Entered as an array using the key combo of CTRL,SHIFT,ENTER:

=COLUMN(INDIRECT(ADDRESS(1,MAX((myrng=A1)*(COLUMN(myrng))))))

Biff
 
M

mjack003

R,

I emailed it to you. Let me know what you come up with. Getting a
error on the line "cells.find(num).activate" because for some of th
values there is nothing to activate because it does not exist on th
"Rows" worksheet. I get an "object variable" error. Thanks for th
input.

Mjac
 
M

mjack003

Biff,

That worked perfect but I'm checking for up to a few thousand entrie
at a time so with that many checks constantly running the speed o
input on the "rows" screen is extremely slow. Is there any way t
automate that into a macro to run once I've input all my numbers?

Thanks,
Mjac
 
D

Dave Peterson

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
 
B

Biff

After thinking about this for awhile it's not as complicated as that formula
seems to make it.

Still array entered:

=MAX((myrng=A1)*(COLUMN(myrng)))

Biff
 
Top