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
|