Need to identify a column within a range that meets criteria


G

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
 
Ad

Advertisements

D

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
 
G

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
 
D

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
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top