AndyPandy27
Board Regular
- Joined
- Jul 17, 2012
- Messages
- 142
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):
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
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 SubIf 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
