Select cells based on named Range and a column heading

[Guest]

Friends,

I have a spreadsheet similar to the following:

A B C
1 Symbol Quant PnL
2 DELL 50
3 INTC 100
4 AMD 200

I have a named Range "Symbol" for cells A2..A4.

I want to select cells C2..C4 using the fact that they are in the column
with the heading of "PnL" and that they correspond to the Range "Symbol".

In other words, I want the code to say, "Select the cells that correspond to
the Range "Symbol" but are in the column with the heading "PnL".

Thanks,
Alan

 

[Vasant Nanavati]

Range("Symbol").Offset(,
WorksheetFunction.Match("PnL",Range("A1:Z1"),0)-1).Select

 

[Guest]

Assuming your headings are in row 1 then try:

Dim fndCell As Range
With Rows(1)
Set fndCell = .Find("Pnl")
If Not fndCell Is Nothing Then
Range("Symbol").Offset(0, fndCell.Column - _
Range("Symbol").Column).Select
End If
End With

Hope this helps
Rowan

 

[Guest]

Rowan and Vasant,

Thanks much for the assistance.

Alan