| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Input formula into a column via macro
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Input formula into a column via macro
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Input formula into a column via macro
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("P2:P1000").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 "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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Input formula into a column via macro
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("P1:P40"), Type:=xlFillDefault Note the top 2 lines have wrapped and are a single line. Adjust the P1:P40 to the actual range you want to fill Mike <ike "franciz" wrote: 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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Input formula into a column via macro
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("P3:P10").FillDown Rick "Rick Rothstein (MVP - VB)" wrote in message ... 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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Input formula into a column via macro
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 "Tim Zych" wrote: 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("P2:P1000").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 "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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Input formula into a column via macro
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 "franciz" a écrit dans le message de ... 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 "Tim Zych" wrote: 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("P2:P1000").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 "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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Input formula into a column via macro
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 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Input formula into a column via macro
Hi Papyjac
Would you elaborate, I don't understand it regards, francis "Papyjac" wrote: 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 "franciz" a écrit dans le message de ... 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 "Tim Zych" wrote: 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("P2:P1000").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 "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 |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Input formula into a column via macro
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 |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Input formula into a column via macro
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 "Rick Rothstein (MVP - VB)" wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| help with formula for last input in column | Excel Worksheet Functions | |||
| inverse of the column function? i.e. input a number, output thecorresponding column text label | Excel Worksheet Functions | |||
| A formula or macro that will place the date in an adjacent column Bany time something is typed in column A | Excel Programming | |||
| run macro with input msg based on cell input | Excel Discussion (Misc queries) | |||
| Macro with input box to Select a column on active sheet | Excel Programming | |||
Linear Mode
