Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 12-12-2019, 06:51 AM
rsrasc rsrasc is offline Help with Formula in a Macro Windows 10 Help with Formula in a Macro Office 2013
Competent Performer
Help with Formula in a Macro
 
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
  #2  
Old 12-13-2019, 06:58 AM
Debaser's Avatar
Debaser Debaser is offline Help with Formula in a Macro Windows 7 64bit Help with Formula in a Macro Office 2010 32bit
Competent Performer
 
Join Date: Oct 2015
Location: UK
Posts: 221
Debaser will become famous soon enough
Default

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

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Help with Formula in a Macro 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
Help with Formula in a Macro 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
Help with Formula in a Macro Need Macro or Formula Help mbocian Excel 2 04-28-2011 02:04 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 02:13 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft