Macro Error Causing Headache...

AndyPandy27

Board Regular
Joined
Jul 17, 2012
Messages
141
Hi All,

I recently sent out a Business Plan document, produced in Excel, to approx. 100 users, each of whom would save their own copy, and populate it with data specific to them.

Soon after I sent it out, I noticed a few errors with some formulae, so wanted to find a way of being able to update the Business Plans quickly and easily, bearing in mind the varying level of user-ability.

I ended up, with the help of someone on this very MrExcel message board, creating a small macro on a brand new workbook, assigning it to a button, and emailing this "fix" to the users. They would then press the button on the "Fix" workbook and allow the macro to make the changes for them. All good so far.

This worked for two macros without any issue - however, I have produced a third macro, and I am receiving the "1004 - Application-defined or object-defined error" error message and, despite a lot of online searching (and consulting of my new MrExcel Excel 2010 VBA and Macros book), I cannot figure out the issue. I appear to have several, almost identical lines of code, all of which work, then for some reason, one of them returns this error message.

My code is below (please note I am brand new to VBA, so please try to forgive the errors, inconsistencies and general inefficiency of the code):

Code:
Sub UpdateReconBusinessPlan()
 
Application.ScreenUpdating = False
Application.DisplayAlerts = False
On Error GoTo Errorcatch

MyTitle = "Business Plan Fix Step 1 of 2"
MyMessage = "You will be asked to locate your saved Business Plan in the next step.  Click OK to continue."
MsgBox MyMessage, vbOKOnly, MyTitle
Dim BusinessPlanFile As String
BusinessPlanFile = Application.GetOpenFilename(FileFilter:="Excel Files (*.xlsm*), *.xlsm", Title:="Please Locate Your Business Plan", MultiSelect:=False)
Workbooks.Open Filename:=BusinessPlanFile, ignoreReadonlyrecommended:=True
 
Sheets("Front Cover").Activate
ActiveSheet.Unprotect Password:=""
            
            Sheets("Front Cover").Range("B11").Select
            ActiveCell.FormulaR1C1 = "=""Target ""&'Data'!R[1]C[1]"
            
            Sheets("Front Cover").Range("B12").Select
            ActiveCell.FormulaR1C1 = "=""Actual Sales ""&'Data'!RC[1]"
            
            Sheets("Front Cover").Range("B13").Select
            ActiveCell.FormulaR1C1 = "=""Daily Run Rate ""&'Data'!R[-1]C[1]"
            
            Sheets("Front Cover").Range("C12").Formula = "=SUMIFS('Sales Data'!$G:$G,'Sales Data'!$L:$L,C$10,'Sales Data'!$N:$N,'Data'!$C$12)"
            Sheets("Front Cover").Range("D12").Formula = "=SUMIFS('Sales Data'!$G:$G,'Sales Data'!$L:$L,D$10,'Sales Data'!$N:$N,'Data'!$C$12)"
            Sheets("Front Cover").Range("E12").Formula = "=SUMIFS('Sales Data'!$G:$G,'Sales Data'!$L:$L,E$10,'Sales Data'!$N:$N,'Data'!$C$12)"
            Sheets("Front Cover").Range("F12").Formula = "=SUMIFS('Sales Data'!$G:$G,'Sales Data'!$L:$L,F$10,'Sales Data'!$N:$N,'Data'!$C$12)"
            
ActiveSheet.Protect Password:=""
 
Sheets("Data").Visible = True
ActiveSheet.Unprotect Password:=""
            
            ActiveSheet.Range("R2").Value = "62"
            ActiveSheet.Range("R3").Value = "62"
            ActiveSheet.Range("R4").Value = "64"
            ActiveSheet.Range("R5").Value = "65"
            ActiveSheet.Range("O2").Value = "Q4"
            ActiveSheet.Range("O3").Value = "Q4"
            ActiveSheet.Range("O4").Value = "Q1"
            ActiveSheet.Range("O5").Value = "Q1"
            ActiveSheet.Range("O6").Value = "Q1"
            ActiveSheet.Range("O7").Value = "Q2"
            ActiveSheet.Range("O8").Value = "Q2"
            ActiveSheet.Range("O9").Value = "Q2"
            ActiveSheet.Range("O10").Value = "Q3"
            ActiveSheet.Range("O11").Value = "Q3"
            ActiveSheet.Range("O12").Value = "Q3"
            ActiveSheet.Range("O13").Value = "Q4"
            ActiveSheet.Range("O14").Value = "Q4"
            ActiveSheet.Range("O15").Value = "Q4"
            ActiveSheet.Range("O16").Value = "Q1"
            ActiveSheet.Range("O17").Value = "Q1"
            ActiveSheet.Range("O18").Value = "Q1"
            ActiveSheet.Range("O19").Value = "Q2"
            ActiveSheet.Range("O20").Value = "Q2"
            ActiveSheet.Range("O21").Value = "Q2"
            ActiveSheet.Range("O22").Value = "Q3"
            ActiveSheet.Range("O23").Value = "Q3"
            ActiveSheet.Range("O24").Value = "Q3"
            ActiveSheet.Range("O25").Value = "Q4"
            ActiveSheet.Range("O26").Value = "Q4"
            ActiveSheet.Range("O27").Value = "Q4"
            ActiveSheet.Range("O28").Value = "Q1"
            ActiveSheet.Range("O29").Value = "Q1"
            ActiveSheet.Range("O30").Value = "Q1"
            ActiveSheet.Range("O31").Value = "Q2"
            ActiveSheet.Range("O32").Value = "Q2"
            ActiveSheet.Range("O33").Value = "Q2"
            ActiveSheet.Range("O34").Value = "Q3"
            ActiveSheet.Range("O35").Value = "Q3"
            ActiveSheet.Range("O36").Value = "Q3"
            ActiveSheet.Range("O37").Value = "Q4"
            ActiveSheet.Range("O38").Value = "Q4"
            ActiveSheet.Range("O39").Value = "Q4"
            ActiveSheet.Range("O40").Value = "Q1"
            ActiveSheet.Range("O41").Value = "Q1"
            ActiveSheet.Range("O42").Value = "Q1"
            ActiveSheet.Range("O43").Value = "Q2"
            ActiveSheet.Range("O44").Value = "Q2"
            ActiveSheet.Range("O45").Value = "Q2"
            ActiveSheet.Range("O46").Value = "Q3"
            ActiveSheet.Range("O47").Value = "Q3"
            ActiveSheet.Range("O48").Value = "Q3"
            ActiveSheet.Range("O49").Value = "Q4"
            ActiveSheet.Range("O50").Value = "Q4"
            ActiveSheet.Range("O51").Value = "Q4"
      
ActiveSheet.Protect Password:=""
Sheets("Data").Visible = xlVeryHidden
        
Sheets("Sales Data").Activate
ActiveSheet.Unprotect Password:=""
            
            ActiveSheet.Range("N:N").Select
            Selection.Insert Shift:=xlToRight
            ActiveSheet.Range("N1").Value = "Quarter"
            ActiveSheet.Range("N2:N20000").Formula = "=VLOOKUP(C2,'Data'!$M$2:$O$51,3,0)"
                        
            ActiveSheet.Range("H3000:H20000").Formula = "=IF(A3000="","",IF(ISNUMBER(SEARCH(""2012"",C3000,1)),""2012"",""2013""))"
                
            ActiveSheet.Range("I3000:I20000").Formula = "=IF(H3000="","",IF(H3000=""2012"",B3000+366,B3000))"
                        
            ActiveSheet.Range("J3000:J20000").Formula = "=IF(I3000="","",IF(I3000>'Front Cover'!$D$4,""No"",""Yes""))"
            
            ActiveSheet.Range("K3000:K20000").Formula = "=IF(A3000="","",IF(H3000=""2012"",F3000/VLOOKUP(C3000,PeriodDays,2,0),G3000/VLOOKUP(C3000,PeriodDays,2,0)))"
                        
            'The line below this one is what causes the error
                                    
            [COLOR=#ff0000][B]ActiveSheet.Range("L3000:L20000").Formula = "=IF(D3000="","",IFERROR(VLOOKUP(D3000,'Data'!$BH$2:$BJ$220,3,0),""))"
[/B][/COLOR]                        
            ActiveSheet.Range("M3000:M20000").Formula = "=IF(D3000="","",IFERROR(VLOOKUP(D3000,'Data'!$BH$2:$BK$220,4,0),""))"
            
            ActiveSheet.Range("A2").Select
                                    
ActiveSheet.Protect Password:=""
 
ActiveWorkbook.Save
ActiveWorkbook.Close
 
Application.ScreenUpdating = True
Application.DisplayAlerts = True
 
MyTitle2 = "Business Plan Fix Step 2 of 2"
MyMessage2 = "Business Plan Successfully Updated."
MsgBox MyMessage2, vbOKOnly, MyTitle2
 
Exit Sub
Errorcatch:
MsgBox Err.Number & " - " & Err.Description
End Sub

If anyone can let me know what might be causing this, I would be incredibly grateful.

Please let me know if I have not provided enough detail or information, and I will be happy to provide it.

Many thanks indeed,

Andy
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
You need to double up the quotes inside the formula

Code:
ActiveSheet.Range("L3000:L20000").Formula = "=IF(D3000="""","""",IFERROR(VLOOKUP(D3000,'Data'!$BH$2:$BJ$220,3,0),""""))"
 
Upvote 0
VoG - you little beauty! That worked perfectly!

I can't believe I missed that... typical!

Thank you very much indeed; wish I had posted this two days ago, instead of trawling the internet for hours.

Now I need to learn how to do it all more efficiently (or learn to perform better checking before I send things out to people).

Thanks again,

Andy
 
Upvote 0

Forum statistics

Threads
1,203,205
Messages
6,054,136
Members
444,703
Latest member
pinkyar23

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top