Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 -- mjack003 ------------------------------------------------------------------------ mjack003's Profile: http://www.excelforum.com/member.php...fo&userid=5141 View this thread: http://www.excelforum.com/showthread...hreadid=468422 |
#2
|
|||
|
|||
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 -- mjack003 ------------------------------------------------------------------------ mjack003's Profile: http://www.excelforum.com/member.php...fo&userid=5141 View this thread: http://www.excelforum.com/showthread...hreadid=468422 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 -- mjack003 ------------------------------------------------------------------------ mjack003's Profile: http://www.excelforum.com/member.php...fo&userid=5141 View this thread: http://www.excelforum.com/showthread...hreadid=468422 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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 |
#8
|
|||
|
|||
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 |
#9
|
|||
|
|||
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 |
#10
|
|||
|
|||
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 -- mjack003 ------------------------------------------------------------------------ mjack003's Profile: http://www.excelforum.com/member.php...fo&userid=5141 View this thread: http://www.excelforum.com/showthread...hreadid=468422 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Arithmetical Mode of Criteria in Multiple Non-Adjacent columns | Excel Worksheet Functions | |||
match and count words | Excel Worksheet Functions | |||
Lookup Table Dilemma | Excel Worksheet Functions | |||
How to group similar column titles together???? | Excel Discussion (Misc queries) | |||
Return non-zero cells in column | Excel Worksheet Functions |