Help with Formula in a Macro

rsrasc · #1 12-12-2019, 06:51 AM

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

Debaser · #2 12-13-2019, 06:58 AM

You should only see that message if the workbook/sheet name in the formula is wrong, so double-check it carefully.

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