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?

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?

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?

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?