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
|