Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need to identify a column within a range that meets criteria
I have 2 wkshts W1 & W2. From W1 I need to query a range (D1:Z1) in W2 to
find the column that contains the value that matches to W1!A1. This should be so simple but like Thomas Edison I now know 1000 ways that won't work. Thanks, Steve |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need to identify a column within a range that meets criteria
Maybe something like this:
Option Explicit Sub testme() Dim WS1 As Worksheet Dim WS2 As Worksheet Dim myCell As Range Dim myRng As Range Dim res As Variant Set WS1 = Worksheets("sheet1") Set WS2 = Worksheets("sheet2") Set myCell = WS1.Range("a1") With WS2 Set myRng = .Range("a1:Z1") End With res = Application.Match(myCell.Value, myRng, 0) If IsError(res) Then MsgBox "no match, the board goes back" Else MsgBox res & vbLf & myRng(res).Address End If End Sub stebro wrote: I have 2 wkshts W1 & W2. From W1 I need to query a range (D1:Z1) in W2 to find the column that contains the value that matches to W1!A1. This should be so simple but like Thomas Edison I now know 1000 ways that won't work. Thanks, Steve -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need to identify a column within a range that meets criteria
Thanks Dave - this works. The one thing that would be better is if I could
isolate just the column. Once I have the column I want to pass that to another function. I'll work on it some more but now I have to give up my coding to go to a dumb-old wedding :) I'll be online later to let you know how things are going. Steve "Dave Peterson" wrote: Maybe something like this: Option Explicit Sub testme() Dim WS1 As Worksheet Dim WS2 As Worksheet Dim myCell As Range Dim myRng As Range Dim res As Variant Set WS1 = Worksheets("sheet1") Set WS2 = Worksheets("sheet2") Set myCell = WS1.Range("a1") With WS2 Set myRng = .Range("a1:Z1") End With res = Application.Match(myCell.Value, myRng, 0) If IsError(res) Then MsgBox "no match, the board goes back" Else MsgBox res & vbLf & myRng(res).Address End If End Sub stebro wrote: I have 2 wkshts W1 & W2. From W1 I need to query a range (D1:Z1) in W2 to find the column that contains the value that matches to W1!A1. This should be so simple but like Thomas Edison I now know 1000 ways that won't work. Thanks, Steve -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need to identify a column within a range that meets criteria
This portion:
MsgBox res & vbLf & myRng(res).Address Showed the index into the lookup range and it showed the address of that cell that matched. If you want just the column: MsgBox myRng(res).column stebro wrote: Thanks Dave - this works. The one thing that would be better is if I could isolate just the column. Once I have the column I want to pass that to another function. I'll work on it some more but now I have to give up my coding to go to a dumb-old wedding :) I'll be online later to let you know how things are going. Steve "Dave Peterson" wrote: Maybe something like this: Option Explicit Sub testme() Dim WS1 As Worksheet Dim WS2 As Worksheet Dim myCell As Range Dim myRng As Range Dim res As Variant Set WS1 = Worksheets("sheet1") Set WS2 = Worksheets("sheet2") Set myCell = WS1.Range("a1") With WS2 Set myRng = .Range("a1:Z1") End With res = Application.Match(myCell.Value, myRng, 0) If IsError(res) Then MsgBox "no match, the board goes back" Else MsgBox res & vbLf & myRng(res).Address End If End Sub stebro wrote: I have 2 wkshts W1 & W2. From W1 I need to query a range (D1:Z1) in W2 to find the column that contains the value that matches to W1!A1. This should be so simple but like Thomas Edison I now know 1000 ways that won't work. Thanks, Steve -- Dave Peterson -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need to identify a column within a range that meets criteria
Thanks so much - I got it! Thanks for your support of this newsgroup!
steve "Dave Peterson" wrote: This portion: MsgBox res & vbLf & myRng(res).Address Showed the index into the lookup range and it showed the address of that cell that matched. If you want just the column: MsgBox myRng(res).column stebro wrote: Thanks Dave - this works. The one thing that would be better is if I could isolate just the column. Once I have the column I want to pass that to another function. I'll work on it some more but now I have to give up my coding to go to a dumb-old wedding :) I'll be online later to let you know how things are going. Steve "Dave Peterson" wrote: Maybe something like this: Option Explicit Sub testme() Dim WS1 As Worksheet Dim WS2 As Worksheet Dim myCell As Range Dim myRng As Range Dim res As Variant Set WS1 = Worksheets("sheet1") Set WS2 = Worksheets("sheet2") Set myCell = WS1.Range("a1") With WS2 Set myRng = .Range("a1:Z1") End With res = Application.Match(myCell.Value, myRng, 0) If IsError(res) Then MsgBox "no match, the board goes back" Else MsgBox res & vbLf & myRng(res).Address End If End Sub stebro wrote: I have 2 wkshts W1 & W2. From W1 I need to query a range (D1:Z1) in W2 to find the column that contains the value that matches to W1!A1. This should be so simple but like Thomas Edison I now know 1000 ways that won't work. Thanks, Steve -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Populate worksheet with data that meets date range criteria | Excel Worksheet Functions | |||
Automatically hide column or row if a cell value meets criteria | Excel Worksheet Functions | |||
RANGE EXCEL copy cell that meets criteria in a range | Excel Worksheet Functions | |||
How do I count in column A when it meets all criteria in three col | Excel Worksheet Functions | |||
Formula to return ADDRESS of cell in range that meets criteria | Excel Worksheet Functions |