Hopefully someone out there can help me as I'm finding very few posts related to the actual error I'm receiving. Most are about Global failed instead of Application failed. I can find only a couple of posts actually related to the error I'm getting, which is Run-time error '1004': Method 'Intersect' of object '_Application' failed. And I apologize in advance for the length of this question. Hopefully all of the background is necessary based on what I'm thinking the error is a result of.
The error is being generated by a very large, complex Macro that is being used to update older versions of a spreadsheet we use for creating a capital improvement plan (for asset management) to the latest version. A separate copy of the workbook is used for each client and making all of the changes to the workbook for each client would be very time-consuming (and ripe with possibilities for errors). Thus the need to write the macro to do the updates consistently and error free.
Among other things that the macro does is to update the "Template" worksheet in the capital improvement plan (CIP) workbook. The "Template" worksheet is used within the CIP workbook to generate a new capital improvement project worksheet when new capital improvement projects are identified. It is generated when the user clicks on the "Create New Capital Improvement Project" button on the "General" worksheet of the CIP workbook. One cell of the existing "Template" worksheet is an overall budget number in current dollars. The update adds a new calculation below that for the estimated budgetary cost in the year the project will be done (i.e. taking up to 20 years' worth of inflation into account). This value is calculated on another worksheet named "Capital Expense Matrix". The calculation is a complex lookup formula in the form of an If statement. The overall if statement that gets put into each of the capital improvement project worksheets is as follows (actual cell references on the "Capital Expense Matrix" worksheet vary depending on the number of capital improvement projects included in the CIP workbook):
=IF(H3='Capital Expense Matrix'!C36, "Current Year", ROUND(VLOOKUP(C7,'Capital Expense Matrix'!B2:Y30,2+MATCH(ROUNDUP(H3,0),'Capital Expense Matrix'!D1:Y1,0),FALSE),-3))
To save you having to try to break that all down, that formula is an if statement that looks at the projected year of the capital improvement project in cell H3 and compares it to the year in the first column of the Capital Expense Matrix worksheet (the year the budgetary prices were developed). If the projected year of the project equals the year in the first column (i.e. if the project is supposed to be done in the year that the budgetary prices were developed), the formula returns a value of "Current Year" in the cell. See the attached images of the "Template" worksheet (with the formula cell highlighted) and a portion of the 'Capital Expense Matrix' worksheet for more details.
If the project is supposed to be done in any other year, the formula uses the VLOOKUP command to look up the name of the capital improvement project (in cell C7) in column B of the "Capital Expense Matrix" worksheet and returns the rounded, inflated cost (rounded up to the nearest $1,000) of the project in the year the project will be done using the MATCH function (the 3rd column of the VLOOKUP range is the current year, and the MATCH function returns the relative column number in the range of D1:Y1 that contains the year the project will be done - i.e. 2 + the column number returned by the MATCH command). The year in cell H3 is rounded to 0 because the workbook allows the user to put the capital improvement projects in order (if there are multiple projects in 1 year) by using a decimal point behind the year (i.e. the first project in 2020 could be 2020.01, the second could be 2020.02, the third could be 2020.03, etc.). Yes - very complicated, but it works like a charm...
...except on the "Template" worksheet. There is no "Template" project in the Capital Expense Matrix worksheet and both the year (in cell H3 of the "Template" worksheet) and the capital improvement project name (in cell C7 of the "Template" worksheet) are blank. As such, when entered into the cell on the "Template" worksheet, the formula results in a "#N/A" error. No big deal because, when the user creates a new capital improvement project using the "Create New..." button, that macro creates a copy of the "Template" worksheet and prompts the user for the year the project will be done and the project title. So, by the time the user sees the new project worksheet (the "Template" worksheet is hidden), the #N/A error is fixed because there is now a year and a title for the project.
However, that is where the "Update" macro is throwing the 'Intersect' of object error. The error is generated in the following lines of the macro:
With wsProj.Range("H5")
.Font.Bold = True
.HorizontalAlignment = xlCenter
.NumberFormat = "$#,###"
.Formula = "=IF(H3='Capital Expense Matrix'!C" & MatrixRow + 6 & ",""Current " _
& "Year"",ROUNDUP(VLOOKUP(C7,'Capital Expense Matrix'!B2:Y" & MatrixRow _
& ",2+MATCH(ROUND(H3,0)," & "'Capital Expense Matrix'!D1:Y1,0),FALSE),-3))"
End With
The .Formula line is the one that is highlighted when you enter the debug mode. But, unlike normal debug mode, I can't drag the yellow arrow to a different part of the code. And the macro has apparently gone so far as inserting the formula because, as the attached Template CIP image shows, it's in the worksheet. But it then gets stuck on that .Formula line (after inserting the formula) and generates the error.
So, can anyone give me any guidance as to why that particular error is being generated or give me any ideas on how to get that formula into the Template worksheet? The formula needs to be there so that new projects created from the template have that inflated cost. I've come up with a couple of work arounds:
1) Edit the "Create New..." macro to have it insert the formula on the new capital improvement project worksheet after the year and project title have been entered.
2) Put a hidden (white text) "Template" project in the bottom row of the Capital Expense Matrix worksheet so the formula does not generate the #N/A error.
I haven't tested either of those work-arounds yet (number 1 would be a bugger because then I'd have to replace one of the workbook macros and I'd prefer not to go that route and number 2 might cause unforeseen errors with other parts of the workbook). But, even if one of those works, I would really like to figure out why this .Formula command is causing so much trouble. Thanks!!!
The error is being generated by a very large, complex Macro that is being used to update older versions of a spreadsheet we use for creating a capital improvement plan (for asset management) to the latest version. A separate copy of the workbook is used for each client and making all of the changes to the workbook for each client would be very time-consuming (and ripe with possibilities for errors). Thus the need to write the macro to do the updates consistently and error free.
Among other things that the macro does is to update the "Template" worksheet in the capital improvement plan (CIP) workbook. The "Template" worksheet is used within the CIP workbook to generate a new capital improvement project worksheet when new capital improvement projects are identified. It is generated when the user clicks on the "Create New Capital Improvement Project" button on the "General" worksheet of the CIP workbook. One cell of the existing "Template" worksheet is an overall budget number in current dollars. The update adds a new calculation below that for the estimated budgetary cost in the year the project will be done (i.e. taking up to 20 years' worth of inflation into account). This value is calculated on another worksheet named "Capital Expense Matrix". The calculation is a complex lookup formula in the form of an If statement. The overall if statement that gets put into each of the capital improvement project worksheets is as follows (actual cell references on the "Capital Expense Matrix" worksheet vary depending on the number of capital improvement projects included in the CIP workbook):
=IF(H3='Capital Expense Matrix'!C36, "Current Year", ROUND(VLOOKUP(C7,'Capital Expense Matrix'!B2:Y30,2+MATCH(ROUNDUP(H3,0),'Capital Expense Matrix'!D1:Y1,0),FALSE),-3))
To save you having to try to break that all down, that formula is an if statement that looks at the projected year of the capital improvement project in cell H3 and compares it to the year in the first column of the Capital Expense Matrix worksheet (the year the budgetary prices were developed). If the projected year of the project equals the year in the first column (i.e. if the project is supposed to be done in the year that the budgetary prices were developed), the formula returns a value of "Current Year" in the cell. See the attached images of the "Template" worksheet (with the formula cell highlighted) and a portion of the 'Capital Expense Matrix' worksheet for more details.
If the project is supposed to be done in any other year, the formula uses the VLOOKUP command to look up the name of the capital improvement project (in cell C7) in column B of the "Capital Expense Matrix" worksheet and returns the rounded, inflated cost (rounded up to the nearest $1,000) of the project in the year the project will be done using the MATCH function (the 3rd column of the VLOOKUP range is the current year, and the MATCH function returns the relative column number in the range of D1:Y1 that contains the year the project will be done - i.e. 2 + the column number returned by the MATCH command). The year in cell H3 is rounded to 0 because the workbook allows the user to put the capital improvement projects in order (if there are multiple projects in 1 year) by using a decimal point behind the year (i.e. the first project in 2020 could be 2020.01, the second could be 2020.02, the third could be 2020.03, etc.). Yes - very complicated, but it works like a charm...
...except on the "Template" worksheet. There is no "Template" project in the Capital Expense Matrix worksheet and both the year (in cell H3 of the "Template" worksheet) and the capital improvement project name (in cell C7 of the "Template" worksheet) are blank. As such, when entered into the cell on the "Template" worksheet, the formula results in a "#N/A" error. No big deal because, when the user creates a new capital improvement project using the "Create New..." button, that macro creates a copy of the "Template" worksheet and prompts the user for the year the project will be done and the project title. So, by the time the user sees the new project worksheet (the "Template" worksheet is hidden), the #N/A error is fixed because there is now a year and a title for the project.
However, that is where the "Update" macro is throwing the 'Intersect' of object error. The error is generated in the following lines of the macro:
With wsProj.Range("H5")
.Font.Bold = True
.HorizontalAlignment = xlCenter
.NumberFormat = "$#,###"
.Formula = "=IF(H3='Capital Expense Matrix'!C" & MatrixRow + 6 & ",""Current " _
& "Year"",ROUNDUP(VLOOKUP(C7,'Capital Expense Matrix'!B2:Y" & MatrixRow _
& ",2+MATCH(ROUND(H3,0)," & "'Capital Expense Matrix'!D1:Y1,0),FALSE),-3))"
End With
The .Formula line is the one that is highlighted when you enter the debug mode. But, unlike normal debug mode, I can't drag the yellow arrow to a different part of the code. And the macro has apparently gone so far as inserting the formula because, as the attached Template CIP image shows, it's in the worksheet. But it then gets stuck on that .Formula line (after inserting the formula) and generates the error.
So, can anyone give me any guidance as to why that particular error is being generated or give me any ideas on how to get that formula into the Template worksheet? The formula needs to be there so that new projects created from the template have that inflated cost. I've come up with a couple of work arounds:
1) Edit the "Create New..." macro to have it insert the formula on the new capital improvement project worksheet after the year and project title have been entered.
2) Put a hidden (white text) "Template" project in the bottom row of the Capital Expense Matrix worksheet so the formula does not generate the #N/A error.
I haven't tested either of those work-arounds yet (number 1 would be a bugger because then I'd have to replace one of the workbook macros and I'd prefer not to go that route and number 2 might cause unforeseen errors with other parts of the workbook). But, even if one of those works, I would really like to figure out why this .Formula command is causing so much trouble. Thanks!!!
