Input formula into a column via macro

[franciz]

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

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Tim Zych]

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

Then if you want to copy it:

Range("P1").Copy
Range("P21000").PasteSpecial xlPasteFormulas
Application.CutCopyMode = False

 

[Mike H]

Try this

Range("P1").FormulaR1C1 =
"=INDEX([FXAppl.xls]Sheet1!R1C3:R100C3,MATCH(LEFT(Sheet3!RC[-15],4)&""*"",[FXAppl.xls]Sheet1!R1C2:R100C2,0))"

Range("P1").AutoFill Destination:=Range("P140"), Type:=xlFillDefault

Note the top 2 lines have wrapped and are a single line. Adjust the P140
to the actual range you want to fill

Mike

<ike

 

[Rick Rothstein \(MVP - VB\)]

By the way, if your intent after placing the formula into the cell is to
copy it down, you can use a statement like this to do that...

Worksheets("Sheet1").Range("P310").FillDown

Rick

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

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

 

[franciz]

Hi Tim

There is an error upon running the code. Error message : Run Time Error "1004"
and this is highlighted : Range("P2").Formula = f

thanks

regards, francis

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

Then if you want to copy it:

Range("P1").Copy
Range("P21000").PasteSpecial xlPasteFormulas
Application.CutCopyMode = False

--
Tim Zych
www.higherdata.com
Compare data in workbooks and find differences with Workbook Compare
A free, powerful, flexible Excel utility

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

 

[Papyjac]

Hello Franciz,

I do not code the formula in VB, but I stocke the formula in the Sheet, for
exemple in Line 1 (same column)
next I copie this formula in the column

With this technique, your code is indépendant of the formula

--
P a p y j a c

Hi Tim

There is an error upon running the code. Error message : Run Time Error
"1004"
and this is highlighted : Range("P2").Formula = f

thanks

regards, francis

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

Then if you want to copy it:

Range("P1").Copy
Range("P21000").PasteSpecial xlPasteFormulas
Application.CutCopyMode = False

--
Tim Zych
www.higherdata.com
Compare data in workbooks and find differences with Workbook Compare
A free, powerful, flexible Excel utility

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

 

[franciz]

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

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

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

 

[franciz]

Hi Papyjac

Would you elaborate, I don't understand it

regards, francis

Hello Franciz,

I do not code the formula in VB, but I stocke the formula in the Sheet, for
exemple in Line 1 (same column)
next I copie this formula in the column

With this technique, your code is indépendant of the formula

--
P a p y j a c

Hi Tim

There is an error upon running the code. Error message : Run Time Error
"1004"
and this is highlighted : Range("P2").Formula = f

thanks

regards, francis

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

Then if you want to copy it:

Range("P1").Copy
Range("P21000").PasteSpecial xlPasteFormulas
Application.CutCopyMode = False

--
Tim Zych
www.higherdata.com
Compare data in workbooks and find differences with Workbook Compare
A free, powerful, flexible Excel utility


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

 

[Rick Rothstein \(MVP - VB\)]

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

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

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

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

 

[franciz]

Hi Rick

Thank for the guide. I wouldn't insert the formula to the last used row
using this
It only place the formula in Q2 and stop there. What need to be add for it
the place
the formula till the last used row?

Dim LastRow As Long

LastRow = Workbooks("FXAppl.xls").Worksheets("Sheet1"). _
Cells(Rows.Count, "B").End(xlUp).Row


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

Thanks

regards, francis

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

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

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


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