Hi Rick,


Rub Time Error "13" appear when I run the code. Highlight this line

Worksheets("Sheet1").Range("Q2").Formula = _
"=IF(ISNA(MATCH(LEFT(Sheet3!A2,4)&" *
",[FXAppl.xls]Sheet1!$B$1:$B$100,0)),""No
Rate"",INDEX([FXAppl.xls]Sheet1!$C$1:$C$100,MATCH(LEFT(Sheet3!A2,4)&" *
",[FXAppl.xls]Sheet1!$B$1:$B$100,0)))"

i have try to show " No rate" instead of #NA.

Can the code stop at the last used rows instead of going down to 1000 rows?

thanks

regards, francis


"Rick Rothstein (MVP - VB)" wrote:

I know your newsreader will wrap this statement, but it should be put into
your macro on a single line...

Worksheets("Sheet1").Range("P3").Formula =
"=INDEX([FXAppl.xls]Sheet1!$C$1:$C$100,MATCH(LEFT(Sheet3!A1,4)&""*"",[FXAppl.xls]Sheet1!$B$1:$B$100,0))"

Obviously, change the worksheet and cell references to suit your actual
needs.

Rick


"franciz" wrote in message
...
Hi

How can I input this formula into col P via a macro

=INDEX([FXAppl.xls]Sheet1!$C$1:$C$100,MATCH(LEFT(Sheet3!A1,4)&"*",[FXAppl.xls]Sheet1!$B$1:$B$100,0))

thanks

regards, francis