Macro Help for Autofilter

[nirav.gangar]

hi

New to the world of excel macro. Problem which i face is a have a static header in row "A". Every day i get information which i add in my excel sheet.

Example:

Deal Ref Inst Id Trade Date Underlying
111 1234 11/1/2011 NASDAQ
222 2468 11/2/2011 NYSC
333 1235 11/3/2011 DOW
444 2470 11/4/2011 NYSC
555 1236 11/5/2011 NASDAQ
666 2472 11/6/2011 DOW
777 1237 11/7/2011 NASDAQ
888 2474 11/8/2011 NYSC
999 1238 11/9/2011 NYSC
1110 2476 11/10/2011 NYSC
1221 1239 11/11/2011 NIFTY
1332 2478 11/12/2011 NASDAQ
111 1234 11/1/2011 NASDAQ
222 2468 11/2/2011 NYSC
333 1235 11/3/2011 DOW
444 2470 11/4/2011 NYSC
555 1236 11/5/2011 NASDAQ
666 2472 11/6/2011 DOW
777 1237 11/7/2011 NASDAQ
888 2474 11/8/2011 NYSC
999 1238 11/9/2011 NYSC
1110 2476 11/10/2011 NYSC
1221 1239 11/11/2011 NIFTY
1332 2478 11/12/2011 NASDAQ

I need to change every day the short name.
Let take example of "NYSE" to "New York Stock Exchange"
When every i record a macro the following result happens

Range("A1").Select
Selection.AutoFilter
ActiveSheet.Range("$A$1:$E$25").AutoFilter Field:=4, Criteria1:="NYSC"
Range("D3").Select
ActiveCell.FormulaR1C1 = "New York Stock Exchange"
Range("D3").Select
Selection.FillDown


Problem
it defines D3 every time i run the macro and change the value to New York Stock Exchange.

i want a macro where in it will be dynamic and select next row and not specific cell after i apply filter.


i know am bad in explaning but still have tried.

 

[asking]

Hi there, Try ~ (Always save your workbook first!)

In the formulas menu, click Define name, in the name box type Nyse, in the refers to box type or copy and paste this ~
=OFFSET(Sheet1!$D$1,1,0,COUNTA(OFFSET(Sheet1!$A$1,1,0,9999)),1)
click ok. Then this code.

Sub nyse()
Range("A1:D1").Select
Selection.AutoFilter Field:=4, Criteria1:="NYSE"
Range("Nyse").Select
Selection.SpecialCells(xlCellTypeVisible) = "New York Stock Exchange"
Selection.AutoFilter
End Sub

You must have at least one NYSE to filter, if you do not it will put New York Stock Exchange in all cells.
cheers