Return a column # from an array
 

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 :mad:


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

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.


--
remove $$$ from email addresss to send email

=========

"mjack003" wrote in
message ...

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 :mad:


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

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

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

Figured out its erroring a number listed on the "audit" sheet can't be
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.


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

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

"mjack003" wrote in
message ...

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 :mad:


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

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" wrote in
message ...

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

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

Figured out its erroring a number listed on the "audit" sheet can't be
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.


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

R,

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

Mjack


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

Biff,

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

Thanks,
Mjack


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

Hi!

Sorry, I can't help with VBA.

Biff

"mjack003" wrote in
message ...

Biff,

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

Thanks,
Mjack


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

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 :mad:

--
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

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

"Biff" wrote in message
...
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

"mjack003" wrote
in message ...

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 :mad:


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