field name copy and paste

[davethewelder]

hi, I am trying to create a formula to lookup a value in a table and if the
value is "Y" then copy the field heading to another sheet. I have tried it
using the attached code but i cannot get it to look up to Row 1. I would try
an Array but I not that confident yet.
sub pick_reason
Dim MyCell As Range
For Each MyCell In ActiveSheet.Range("AD2:AH2").Cells

Select Case True
Case MyCell = "Y"
MyCell = ActiveCell.Offset(0, -1)


'Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
Range("F1").Select
ActiveSheet.Paste
Sheets("Sheet1").Select

End Select
Next MyCell
End Sub


the table is

Issue Status Not Subsector Priority Not Relevant to Firms
Business Previously Investigated Investigated in Discovery Other
Closed N N N Y N
Closed N N N N N
Closed N N Y N N
Closed Y Y N N N
Closed N N N N N
Closed N N N N Y


Hope someone can help.

Davie

 

[Tom van Stiphout]

On Fri, 7 Mar 2008 04:05:01 -0800, davethewelder

You need to study the VLOOKUP function.
And you should post in an Excel-based newsgroup; this one is about the
Microsoft Access database system.

-Tom.

 

[davethewelder]

Tom, you are correct. I am using Access and Excel at present and got mixed
up. VLookups done the trick.

thanks.

Davie