Thread: Help with Formula in a Macro
View Single Post
 
Old 12-12-2019, 06:51 AM
rsrasc rsrasc is offline Windows 10 Office 2013
Competent Performer
  
Join Date: Mar 2014
Location: Germany
Posts: 148
rsrasc is on a distinguished road
Default 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))"
Reply With Quote