Code erroring out.

Ok here is the code that is breaking:

If Range("useSP").Value = "Y" Then
startpoint1 = 1
startpoint2 = 1
Else
startpoint1 = 144
startpoint2 = 943
End If

'Inputing formulas and making needed properity changes
For x = 0 To 142
With Sheets("Pyramid Evolution").Range(Range("SalaryPointPE").Cells(startpoint1 + x, 1).Value)
.Formula = Range("SalaryPointPE").Cells(startpoint1 + x, 2).Value
.Interior.Color = Range("SalaryPointPE").Cells(startpoint1 + x, 3).Value
.Locked = Range("SalaryPointPE").Cells(startpoint1 + x, 4).Value
End With
Next x
breakvalue1 = 190
breakvalue2 = 200
For y = 0 To 941
With Sheets("Perf Mgmt Design").Range(Range("SalaryPointPMD").Cells(startpoint2 + y, 1).Value)
If y > breakvalue1 And y < breakvalue2 Then
MsgBox "startpoint2 = " & startpoint2 & " y = " & y & _
" Cell range = " & Range("SalaryPointPMD").Cells(startpoint2 + y, 1).Value & _
" Formula will = " & Range("SalaryPointPMD").Cells(startpoint2 + y, 2).Value & _
" Color will = " & Range("SalaryPointPMD").Cells(startpoint2 + y, 3).Value & _
" and Locked is " & Range("SalaryPointPMD").Cells(startpoint2 + y, 4).Value
End If
.Formula = Range("SalaryPointPMD").Cells(startpoint2 + y, 2).Value
.Interior.Color = Range("SalaryPointPMD").Cells(startpoint2 + y, 3).Value
.Locked = Range("SalaryPointPMD").Cells(startpoint2 + y, 4).Value
End With
Next y

End Sub

First off the message box inside the loop is showing all the values around the point of the error and have been using it to troubleshoot.
This code works perfectly as long as startpoint2 does not equal 1. When it does then in the process of looping thru the named range at item 192 it fails to make use of the value that is contained within that cell. When viewing the supposed outcome with a message box all needed values can be seen but yet when I run this from within excel I get an Error 400 and if I run this from inside the VB Editor I get run-time error '1004' Application-defined or object-definded error.

Why would this stop working in the middle when all the values are there and the workbook and worksheets are unlocked but work completely when starting in the bottom half of the named range

Answer this question

Code erroring out.

codepath
Thanks for all that looked at this but I found the problem. It was in the formula for Cell P51, it was missing a comma for the if statement.

AsimK

Forgot to show what I am putting in. Here is a snap shot of the data;

Cell Formula Color Locked

P44	='Rated S@L'!O$219	16777215	TRUE
P45	='Rated S@L'!O$220	16777215	TRUE
P46	='Rated S@L'!O$221	16777215	TRUE
P51	=if(P$142=0,0P52/P$142)	16777215	TRUE
P52	='Rated S@L'!E$213	16777215	TRUE
P53	='Rated S@L'!E$215	16777215	TRUE
P55	='Rated S@L'!P$215	16777215	TRUE
P56	='Rated S@L'!P$217	16777215	TRUE

the error occures when it has to deal with cell P51.

Code erroring out.

Answer this question