Subscript 9 error 1

[vba317]

I am using excel 2010.

I am getting a runtime error 9 sbscript out of range
on the line I have highlighted in blue.

Any help would be appreciated

Here is my code:

Sub HighLow_2()


'Created 1/2/2012
Dim LowLoopStart As Double
Dim LowLoopEnd As Double
Dim LowCellValue()
Dim HighCellValue()
Dim Value As Double
Dim MinValue As Double
Dim MaxValue As Double
Dim Diff()
Dim Cell As Range
Dim HighLoopStart As Double
Dim HighLoopEnd As Double
Dim MaxCellCount As Double

Dim Buy1 As Double
Dim Buy2 As Double
Dim Buy3 As Double
Dim Buy4 As Double

Dim Sell1 As Double
Dim Sell2 As Double
Dim Sell3 As Double
Dim Sell4 As Double

Dim SellValue1 As Double
Dim SellValue2 As Double
Dim SellValue3 As Double
Dim SellValue4 As Double

Dim Stop1 As Double
Dim Stop2 As Double
Dim Stop3 As Double
Dim Stop4 As Double

Dim StopValue1 As Double
Dim StopValue2 As Double
Dim StopValue3 As Double
Dim StopValue4 As Double

Dim Buy0786 As Double
Dim Buy1000 As Double
Dim Buy1236 As Double
Dim Buy1382 As Double
Dim Buy1500 As Double

Dim Stop0786 As Double
Dim Stop1000 As Double
Dim Stop1236 As Double
Dim Stop1382 As Double
Dim Stop1500 As Double

'Dims for opening more than 1 worksheet
Dim ws As Worksheet
Dim lCount As Long
Dim wbResults As Workbook
Dim wbCodeBook As Workbook
Dim LastRow As Double
Dim SetScrollRow As Double
    
    
    

ReDim Preserve LowCellValue(0 To LastRow, 0 To LastRow)
ReDim Preserve HighCellValue(0 To LastRow, 0 To LastRow)
ReDim Preserve Diff(0 To 2, 0 To LastRow)
'Set up LowLoopCount LoopStart and LoopEnd values for initial loop

 ' Call BulkQuotesXL.UpdateData
    For Each ws In Worksheets

                        'count Last Row
    LastRow = ActiveSheet.UsedRange.Rows.Count

        With ws
        .Activate
        Range("A3").Select
                                              
        Select Case .Name
        'This step  skips the sheet named BulkquotesXlSettings
        Case "BulkQuotesXL Settings"
                            
        Case Else
        'Freeze Panes
         Rows("2:2").Select
         ActiveWindow.FreezePanes = True

        'End With
         End Select
     
                                
                             
                                
        'Code goes here

        If LowValueCount = 0 Then LowValueCount = 2
        If LowLoopCount = 0 Then LowLoopStart = 2
        If LowLoopCount = 0 Then LowLoopEnd = 9

        'Buy, Sell and stop signals

        Buy1 = 0.786
        Buy2 = 1
        Buy3 = 1.236
        Buy4 = 1.382
        Buy5 = 1.5

        Sell1 = 0.05
        Sell2 = 0.1
        Sell3 = 0.15
        Sell4 = 0.2

        SellValue1 = Sell1 * 100
        SellValue2 = Sell2 * 100
        SellValue3 = Sell3 * 100
        SellValue4 = Sell4 * 100

        Stop1 = 0.025
        Stop2 = 0.05
        Stop3 = 0.075
        Stop4 = 1

        StopValue1 = Stop1 * 100
        StopValue2 = Stop2 * 100
        StopValue3 = Stop3 * 100
        StopValue4 = Stop4 * 100


 

            For LowLoopCount = LowLoopStart To LowLoopEnd

            'Set up loop LowLoopCount LoopStart and LoopEnd values for all loops

                If LowLoopCount > 2 Then LowLoopStart = HighLoopEnd + 1
                If LowLoopCount > 2 Then LowLoopEnd = HighLoopEnd + 7
                HighLoopStart = LowLoopEnd + 1
                HighLoopEnd = LowLoopEnd + 7

                Value = Value + 1
                                    
                    If LowLoopEnd >= LastRow Then LowLoopEnd = LastRow

                    'Calculate Range for MinValue
                    MinValue = Application.WorksheetFunction.Min(Range("D" & LowLoopStart & ":" & "D" & LowLoopEnd))
    
                    'Calculate Cell that Minvalue is in
                        For Each Cell In Range("D" & LowLoopStart & ":" & "D" & LowLoopEnd)
    
                        If Cell.Value = MinValue Then _
                        Cell.Interior.Color = vbRed

                 [blue]       If Cell.Value = MinValue Then _
                        LowCellValue(1, Value) = MinValue

[/blue]
                        'If Cell.Value = MinValue Then _
                        MinValue = LowCellValue(1, Value)

                        'End If

                        Next Cell 'For Each Cell In Range("D" & LowLoopStart & ":" & "D" & LowLoopEnd)
                                            
                                            
                                            
                            If HighLoopStart > LastRow Then Exit Sub
                            If HighLoopEnd > LastRow Then Exit Sub

                                For Each Cell In Range("C" & HighLoopStart & ":" & "C" & HighLoopEnd)

                                'Turn Cell green that Maxvalue is in
                                    If Cell.Value = MaxValue Then
                                    Cell.Interior.Color = vbGreen
        
   
                                    'Assign MaxValue
                                    'HighCellValue(1, Value) = MaxValue
   
                                    MaxValue = HighCellValue(1, Value)
   
                                    'Calculate Difference between Max and Min value
                                     Diff(1, Value) = (MaxValue - MinValue)
                                                
                                    'Calculate First Buy Point
                                    Buy0786 = MaxValue - (Diff(1, Value) * Buy1)
                                    Range("G" & Cell.Row).Value = Buy0786
                                                
                                    Stop0786 = (Buy0786 - (Buy0786 * Stop1))
                                    Range("H" & Cell.Row).Value = Stop0786
                                                
                                    Sell0786 = Buy0786 + (Buy0786 * Sell2)
                                    Range("I" & Cell.Row).Value = Sell0786
  
                                    Buy1000 = MaxValue - (Diff(1, Value) * Buy2)
                                    Range("J" & Cell.Row).Value = Buy1000
                                                
                                    Stop1000 = (Buy1000 - (Buy1000 * Stop1))
                                    Range("K" & Cell.Row).Value = Stop1000
                                                
                                    Sell1000 = Buy1000 + (Buy1000 * Sell2)
                                    Range("L" & Cell.Row).Value = Sell1000
  
                                    Buy1236 = MaxValue - (Diff(1, Value) * Buy3)
                                    Range("M" & Cell.Row).Value = Buy1236
                                                
                                    Stop1236 = (Buy1236 - (Buy1236 * Stop1))
                                    Range("N" & Cell.Row).Value = Stop1236
                                                
                                    Sell1236 = Buy1236 + (Buy1236 * Sell2)
                                    Range("O" & Cell.Row).Value = Sell1236
  
                                    Buy1382 = MaxValue - (Diff(1, Value) * Buy4)
                                    Range("P" & Cell.Row).Value = Buy1382
                                                
                                    Stop1382 = (Buy1382 - (Buy1382 * Stop1))
                                    Range("Q" & Cell.Row).Value = Stop1382
                                                
                                    Sell1382 = Buy1382 + (Buy1382 * Sell2)
                                    Range("R" & Cell.Row).Value = Sell1382

                                    Buy1500 = MaxValue - (Diff(1, Value) * Buy5)
                                    Range("S" & Cell.Row).Value = Buy1500
                                                
                                    Stop1500 = (Buy1500 - (Buy1500 * Stop1))
                                    Range("T" & Cell.Row).Value = Stop1500
                                                
                                    Sell1500 = Buy1500 + (Buy1500 * Sell2)
                                    Range("U" & Cell.Row).Value = Sell1500
 
                                    End If 'If Cell.Value = MaxValue Then

                                Next Cell 'For Each Cell In Range("C" & HighLoopStart & ":" & "C" & HighLoopEnd)

                            Next LowLoopCount 'For LowLoopCount = LowLoopStart To LowLoopEnd
                
                     
     
                        
      
      End With

    Next 'For Each ws In Worksheets

End Sub

 

[SkipVought]

What is the value of Value?
[ code]
LowCellValue(1, Value)
[/code]

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Andrzejek]

Skip's question would be my second one.
First one would be:

What is the value of LastRow in:
[tt]
ReDim Preserve LowCellValue(0 To LastRow, 0 To LastRow)
[/tt]

Have fun.

---- Andy

 

[Andrzejek]

I see:

Dim [blue]LastRow[/blue] As Double

ReDim Preserve LowCellValue(0 To [blue]LastRow[/blue], 0 To [blue]LastRow[/blue])
ReDim Preserve HighCellValue(0 To [blue]LastRow[/blue], 0 To [blue]LastRow[/blue])
ReDim Preserve Diff(0 To 2, 0 To [blue]LastRow[/blue])

So you pretty much have:

Dim [blue]LastRow[/blue] As Double

ReDim Preserve LowCellValue(0 To [blue]0[/blue], 0 To [blue]0[/blue])
ReDim Preserve HighCellValue(0 To [blue]0[/blue], 0 To [blue]0[/blue])
ReDim Preserve Diff(0 To 2, 0 To [blue]0[/blue])

Have fun.

---- Andy

 

[vba317]

I see my problem thank for the info

 

[SkipVought]

please explain what you actually did to solve your problem, for the benefit of other members who browse this thread.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[vba317]

The last row formula is
LastRow = ActiveSheet.UsedRange.Rows.Count
I had to move the formula so it was above the
Redim statements

ReDim Preserve LowCellValue(0 To LastRow, 0 To LastRow)
ReDim Preserve HighCellValue(0 To LastRow, 0 To LastRow)
ReDim Preserve Diff(0 To 2, 0 To LastRow)

and the value for the first loop is 1 and so on for each loop