This is a completely different formula than you asked about originally. I'm
guessing the statement I provided you in my last message worked for you
(since you are not writing back about it). When you created your new Formula
assignment statement, you did not follow the pattern I set up for that
original Formula assignment statement. You have this...

&" * "&

in your code twice... in both places, you should have this instead...

&"" * ""&

(note the doubling up of the quote marks) although the spaces around the
asterisk are not necessary. Here is your formula, revised for the above...

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)))"

As for changing the formula so it doesn't "go down to 1000 rows"... it
doesn't do that now. I'm assuming you meant 100 rows. The answer is yes, but
the formula will look messier. I see that you have two column references
going down to Row 100. You will need to pick one of them and that one must
**always** be filled with data to the "last row". Assuming Column B does
that, here is how you calculate the last row...

' Put this statement with the rest of your Dim statements
Dim LastRow As Long
'.......
'.......
LastRow = Workbooks("FXAppl.xls").Worksheets("Sheet1"). _
Cells(Rows.Count, "B").End(xlUp).Row

And here is how the above Formula assignment statement would look with it
incorporated into it...

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

Remember, these Formula assignment statements are supposed to be all on one
line.

Rick


"franciz" wrote in message
...
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