#1
|
|||
|
|||
Help with Formula in a Macro
Hi all,
I want to use the following formula with a macro so I can apply it to at least 20 different excel files. I tested the macro and is working fine up to a certain point. BTW, I'm trying to copy the formula to five (5) different columns. In my file, the columns are from I through M. Once I run the macro, a pop up window will come out asking me to open the source document even when the source document is already open. After I click over the the source document and hit OK, it will ask me four more times to do the same. My question is the following, would it be possible to just copy the formula without having to click so I can open the source document? Also, would it be possible to combine the formula with the following info: UpdateLinks:=0 Just wondering if there is an easy way of accomplishing this. Thanks in advance. Regards, rsrasc HTML Code:
Workbooks("025 FY 2021 Expenses-SSX-Budget.xlsx").Sheets("FY 20 Actual + Reforecast").Range("I8:I30").Formula = "=IF(ISNA(VLOOKUP($A8,'[FY 20-SSE-SSX-7-5 Reforecast-May 20-Sep 20.xlsm]025-SSX'!$A$10:$N$83,10,FALSE)),""0"",VLOOKUP($A8,'[FY 20-SSE-SSX-7-5 Reforecast-May 20-Sep 20.xlsx]025-SSX'!$A$10:$N$83,10,FALSE))" Workbooks("025 FY 2021 Expenses-SSX-Budget.xlsx").Sheets("FY 20 Actual + Reforecast").Range("J8:J30").Formula = "=IF(ISNA(VLOOKUP($A8,'[FY 20-SSE-SSX-7-5 Reforecast-May 20-Sep 20.xlsm]025-SSX'!$A$10:$N$83,11,FALSE)),""0"",VLOOKUP($A8,'[FY 20-SSE-SSX-7-5 Reforecast-May 20-Sep 20.xlsx]025-SSX'!$A$10:$N$83,11,FALSE))" Workbooks("025 FY 2021 Expenses-SSX-Budget.xlsx").Sheets("FY 20 Actual + Reforecast").Range("K8:K30").Formula = "=IF(ISNA(VLOOKUP($A8,'[FY 20-SSE-SSX-7-5 Reforecast-May 20-Sep 20.xlsm]025-SSX'!$A$10:$N$83,12,FALSE)),""0"",VLOOKUP($A8,'[FY 20-SSE-SSX-7-5 Reforecast-May 20-Sep 20.xlsx]025-SSX'!$A$10:$N$83,12,FALSE))" Workbooks("025 FY 2021 Expenses-SSX-Budget.xlsx").Sheets("FY 20 Actual + Reforecast").Range("L8:L30").Formula = "=IF(ISNA(VLOOKUP($A8,'[FY 20-SSE-SSX-10-2 Reforecast-Aug 20-Sep 20.xlsm]025-SSX'!$A$10:$N$83,13,FALSE)),""0"",VLOOKUP($A8,'[FY 20-SSE-SSX-10-2 Reforecast-Aug 20-Sep 20.xlsx]025-SSX'!$A$10:$N$83,13,FALSE))" Workbooks("025 FY 2021 Expenses-SSX-Budget.xlsx").Sheets("FY 20 Actual + Reforecast").Range("M8:M30").Formula = "=IF(ISNA(VLOOKUP($A8,'[FY 20-SSE-SSX-10-2 Reforecast-Aug 20-Sep 20.xlsm]025-SSX'!$A$10:$N$83,14,FALSE)),""0"",VLOOKUP($A8,'[FY 20-SSE-SSX-10-2 Reforecast-Aug 20-Sep 20.xlsx]025-SSX'!$A$10:$N$83,14,FALSE))" |
#2
|
||||
|
||||
You should only see that message if the workbook/sheet name in the formula is wrong, so double-check it carefully.
|
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
auto sort data formula without macro | monisankar | Excel | 2 | 01-15-2017 06:54 PM |
Help With Copy Formula Down Macro | npalmer610 | Excel Programming | 4 | 03-14-2016 01:15 PM |
Macro will not read the value of cell with a formula in it! | grayson1231 | Excel Programming | 10 | 03-28-2015 03:47 PM |
Macro to copy formula from one cell to another | anwar | Excel Programming | 1 | 04-25-2014 08:27 PM |
Need Macro or Formula Help | mbocian | Excel | 2 | 04-28-2011 02:04 AM |