Return a column # from an array

[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

 

[R.VENKATARAMAN]

your numbers are in column A in sheet "audit"
copy this small programme in vbeditor and run
Public Sub test()
Dim num As Single
Dim mynum As Range
Dim cell As Range
Dim colnum As Integer
Worksheets("audit").Select
Set mynum = Range(Range("a1"), Range("a1").End(xlDown))
For Each cell In mynum
num = cell.Value
Worksheets("row").Activate
Cells.Find(num).Activate
colnum = ActiveCell.Column
Worksheets("audit").Select
cell.Offset(0, 1) = colnum
Next
End Sub

do ;you get what you want
modify to suit you.

 

[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

 

[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

 

[R.VENKATARAMAN]

presume your reply below to my suggestion
it worked in my sample file
perhaps the entries in col A are not n;umbers but texts
if you solved the problem on Biff's sophisticated suggestion it is ok
otherwise send a small extract of your file to my yahoo address as attchment
remove dollars from the email address below
venkat1926@$$$yahoo.$$$com
I shall try to find any bug in my suggestion.
regards

 

[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

 

[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

 

[Biff]

Hi!

Sorry, I can't help with VBA.

Biff

 

[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

 

[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