Need to identify a column within a range that meets criteria

[Guest]

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]

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

 

[Guest]

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]

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

 

[Guest]

Thanks so much - I got it! Thanks for your support of this newsgroup!

steve