Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Crashing
My macro keeps crashing. Here is the error message and applicabe code:
Subscript out of range in Project1.Form1.FormatSpread at line 6960 6960 ActiveWorkbook.Worksheets("BaseBid").Activate Range("C35").Select ActiveSheet.Unprotect "Rj6823Lkt2" ActiveCell.FormulaR1C1 = "=Labor!R[-34]C[3]" ActiveSheet.Protect Password:="Rj6823Lkt2" ActiveWorkbook.Worksheets("JobCost").Activate Range("D88").Select ActiveSheet.Unprotect "Rj6823Lkt2" ActiveCell.FormulaR1C1 = "=(SUMIF(Labor!R3C2:R499C2,RC[-3],Labor!R3C6:R499C6))*.75+SUMIF(Worksheet!R4C5:R173 C5,RC[-3],Worksheet!R4C1:R173C1)+SUMIF(BaseBid!R34C2:R77C2, RC[-3],BaseBid!R34C7:R77C7)" Selection.AutoFill Destination:=Range("D88:D242"), Type:=xlFillDefault Range("A9").Select Range("A7").Select ActiveSheet.Protect Password:="Rj6823Lkt2" Any help? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Crashing
Hi
The error message indicate that the sheet doesn't exist in the active workbook, or it may be due to a typo. As you use the same password through out your code, I would use a variable to hold the password, then you also decrease the risk for a typo: Dim MyPassWd As String MyPassWd = "Rj6823Lkt2" Worksheets("BaseBid").Unprotect Password:=MyPassWd ActiveSheet.Unprotect MyPassWd Range("C35").FormulaR1C1 = "=Labor!R[-34]C[3]" ActiveSheet.Protect Password:=MyPassWd Worksheets("JobCost").Activate ActiveSheet.Unprotect MyPassWd Range("D88").FormulaR1C1 = _ "=(SUMIF(Labor!R3C2:R499C2,RC[-3],Labor!R3C6:R499C6))*.75+SUMIF(Worksheet!R4C5:R173 C5,RC[-3],Worksheet!R4C1:R173C1)+SUMIF(BaseBid!R34C2:R77C2, RC[-3],BaseBid!R34C7:R77C7)" Range("D88").AutoFill Destination:=Range("D88:D242"), Type:=xlFillDefault ActiveSheet.Protect Password:=MyPassWd Regards, Per "mickey" skrev i meddelelsen ... My macro keeps crashing. Here is the error message and applicabe code: Subscript out of range in Project1.Form1.FormatSpread at line 6960 6960 ActiveWorkbook.Worksheets("BaseBid").Activate Range("C35").Select ActiveSheet.Unprotect "Rj6823Lkt2" ActiveCell.FormulaR1C1 = "=Labor!R[-34]C[3]" ActiveSheet.Protect Password:="Rj6823Lkt2" ActiveWorkbook.Worksheets("JobCost").Activate Range("D88").Select ActiveSheet.Unprotect "Rj6823Lkt2" ActiveCell.FormulaR1C1 = "=(SUMIF(Labor!R3C2:R499C2,RC[-3],Labor!R3C6:R499C6))*.75+SUMIF(Worksheet!R4C5:R173 C5,RC[-3],Worksheet!R4C1:R173C1)+SUMIF(BaseBid!R34C2:R77C2, RC[-3],BaseBid!R34C7:R77C7)" Selection.AutoFill Destination:=Range("D88:D242"), Type:=xlFillDefault Range("A9").Select Range("A7").Select ActiveSheet.Protect Password:="Rj6823Lkt2" Any help? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Crashing
Thanks. That's great info on the password.
The sheet does exist. So, it probably is a typo, I just can't figure out where it is. "Per Jessen" wrote: Hi The error message indicate that the sheet doesn't exist in the active workbook, or it may be due to a typo. As you use the same password through out your code, I would use a variable to hold the password, then you also decrease the risk for a typo: Dim MyPassWd As String MyPassWd = "Rj6823Lkt2" Worksheets("BaseBid").Unprotect Password:=MyPassWd ActiveSheet.Unprotect MyPassWd Range("C35").FormulaR1C1 = "=Labor!R[-34]C[3]" ActiveSheet.Protect Password:=MyPassWd Worksheets("JobCost").Activate ActiveSheet.Unprotect MyPassWd Range("D88").FormulaR1C1 = _ "=(SUMIF(Labor!R3C2:R499C2,RC[-3],Labor!R3C6:R499C6))*.75+SUMIF(Worksheet!R4C5:R173 C5,RC[-3],Worksheet!R4C1:R173C1)+SUMIF(BaseBid!R34C2:R77C2, RC[-3],BaseBid!R34C7:R77C7)" Range("D88").AutoFill Destination:=Range("D88:D242"), Type:=xlFillDefault ActiveSheet.Protect Password:=MyPassWd Regards, Per "mickey" skrev i meddelelsen ... My macro keeps crashing. Here is the error message and applicabe code: Subscript out of range in Project1.Form1.FormatSpread at line 6960 6960 ActiveWorkbook.Worksheets("BaseBid").Activate Range("C35").Select ActiveSheet.Unprotect "Rj6823Lkt2" ActiveCell.FormulaR1C1 = "=Labor!R[-34]C[3]" ActiveSheet.Protect Password:="Rj6823Lkt2" ActiveWorkbook.Worksheets("JobCost").Activate Range("D88").Select ActiveSheet.Unprotect "Rj6823Lkt2" ActiveCell.FormulaR1C1 = "=(SUMIF(Labor!R3C2:R499C2,RC[-3],Labor!R3C6:R499C6))*.75+SUMIF(Worksheet!R4C5:R173 C5,RC[-3],Worksheet!R4C1:R173C1)+SUMIF(BaseBid!R34C2:R77C2, RC[-3],BaseBid!R34C7:R77C7)" Selection.AutoFill Destination:=Range("D88:D242"), Type:=xlFillDefault Range("A9").Select Range("A7").Select ActiveSheet.Protect Password:="Rj6823Lkt2" Any help? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Crashing
The first thing to test is whether the ActiveWorkbook (the workbook
open in Excel, which may not be the same workbook that contains the code) does indeed have a sheet named "BaseBid". Ensure that you don't have any spaces before, within, or after the worksheet name. Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2009 Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Thu, 19 Mar 2009 11:36:06 -0700, mickey wrote: My macro keeps crashing. Here is the error message and applicabe code: Subscript out of range in Project1.Form1.FormatSpread at line 6960 6960 ActiveWorkbook.Worksheets("BaseBid").Activate Range("C35").Select ActiveSheet.Unprotect "Rj6823Lkt2" ActiveCell.FormulaR1C1 = "=Labor!R[-34]C[3]" ActiveSheet.Protect Password:="Rj6823Lkt2" ActiveWorkbook.Worksheets("JobCost").Activate Range("D88").Select ActiveSheet.Unprotect "Rj6823Lkt2" ActiveCell.FormulaR1C1 = "=(SUMIF(Labor!R3C2:R499C2,RC[-3],Labor!R3C6:R499C6))*.75+SUMIF(Worksheet!R4C5:R173 C5,RC[-3],Worksheet!R4C1:R173C1)+SUMIF(BaseBid!R34C2:R77C2, RC[-3],BaseBid!R34C7:R77C7)" Selection.AutoFill Destination:=Range("D88:D242"), Type:=xlFillDefault Range("A9").Select Range("A7").Select ActiveSheet.Protect Password:="Rj6823Lkt2" Any help? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I prevent an error from crashing my macro? | Excel Programming | |||
Crashing Macro, Not Enough Memory | Excel Programming | |||
Help with a crashing Macro needed | Excel Programming | |||
Macro crashing on different computer | Excel Programming | |||
Macro Crashing Excel | Excel Programming |