Macro Crashing

[mickey]

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:R173C5,RC[-3],Worksheet!R4C1:R173C1)+SUMIF(BaseBid!R34C2:R77C2,RC[-3],BaseBid!R34C7:R77C7)"
Selection.AutoFill Destination:=Range("D88242"),
Type:=xlFillDefault
Range("A9").Select
Range("A7").Select
ActiveSheet.Protect Password:="Rj6823Lkt2"

Any help?

 

[Per Jessen]

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:R173C5,RC[-3],Worksheet!R4C1:R173C1)+SUMIF(BaseBid!R34C2:R77C2,RC[-3],BaseBid!R34C7:R77C7)"
Range("D88").AutoFill Destination:=Range("D88242"), Type:=xlFillDefault
ActiveSheet.Protect Password:=MyPassWd

Regards,
Per

 

[mickey]

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.

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:R173C5,RC[-3],Worksheet!R4C1:R173C1)+SUMIF(BaseBid!R34C2:R77C2,RC[-3],BaseBid!R34C7:R77C7)"
Range("D88").AutoFill Destination:=Range("D88242"), Type:=xlFillDefault
ActiveSheet.Protect Password:=MyPassWd

Regards,
Per

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:R173C5,RC[-3],Worksheet!R4C1:R173C1)+SUMIF(BaseBid!R34C2:R77C2,RC[-3],BaseBid!R34C7:R77C7)"
Selection.AutoFill Destination:=Range("D88242"),
Type:=xlFillDefault
Range("A9").Select
Range("A7").Select
ActiveSheet.Protect Password:="Rj6823Lkt2"

Any help?

 

[Chip Pearson]

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)