Defining Array

[vba317]

I have tried to use a for next loop to solve a problem that I now realize needs a dynamic array to solve. I have never used a dynamic array before so I am hoping that I can get some help. What this loop does is define a high value in the C column and a low value in column D.

My current loop is like this:

'Low Calculation minus 1 cell
    Dim LowM1 As Double
    'Low Calculation minus 2 cells
    Dim LowM2 As Double
    'Low Calculation plus 1 cell
    Dim LowP1 As Double
    'Low Calculation plus 2 cells
    Dim LowP2 As Double
    'High Calculation minus 1 cell
    Dim HighM1 As Double
    'High Calculation minus 2 cells
    Dim HighM2 As Double
    'High Calculation plus 1 cell
    Dim HighP1 As Double
    'High Calculation plus 2 cells
    Dim HighP2 As Double
    'Low Comparison value Minus 1 cell
    Dim LCVM1 As Double
    'Low comparison value Minus 2 cells
    Dim LCVM2 As Double
    'Low comparison value plus 1 cell
    Dim LCVP1 As Double
    'Low comparison value plus 2 cells
    Dim LCVP2 As Double
    'High comparison value plus 2 cells
    Dim HCVP2 As Double
    'High comparison value plus 1 cell
    Dim HCVP1 As Double
    'High comparison value minus 1 cell
    Dim HCVM1 As Double
    'High comparison value minus 2 cells
    Dim HCVM2 As Double
    

For R1 = 4 To LR
                                                            'Min Value Calculation
'A leg Low location
LowM2 = (Range("D" & R1) - Range("D" & R1 - 2))
LowM1 = (Range("D" & R1) - Range("D" & R1 - 1))
LowP1 = Range("D" & R1 + 1) - Range("D" & R1)
LowP2 = Range("D" & R1 + 2) - Range("D" & R1)


' Max Value Calculation
'B leg High location
HighM2 = (Range("C" & R1) - Range("C" & R1 - 2))
HighM1 = (Range("C" & R1) - Range("C" & R1 - 1))
HighP1 = Range("C" & R1 + 1) - Range("C" & R1)
HighP2 = Range("C" & R1 + 2) - Range("C" & R1)
                                    
LCVM2 = 0
LCVM1 = 0
LCVP1 = 0
LCVP2 = 0
HCVM2 = 0
HCVM1 = 0
HCVP1 = 0
HCVP2 = 0
                                    
'E = 1
                                    
'A leg Low header
 Range("H1").Value = "Low"

If LowM2 < LCVM2 And LowM1 < LCVM1 And LowP1 > LCVP1 And LowP2 > LCVP2 Then Range("H" & R1).Value = (Range("D" & R1))

If LowM2 < LCVM2 And LowM1 < LCVM1 And LowP1 > LCVP1 And LowP2 > LCVP2 Then Range("D" & R1).Interior.Color = vbRed
                                    
'B leg High header
Range("G1").Value = "High"

If HighM2 > HCVM2 And HighM1 > HCVM1 And HighP1 < HCVP1 And HighP2 < HCVP2 Then Range("G" & R1).Value = (Range("C" & R1))

If HighM2 > HCVM2 And HighM1 > HCVM1 And HighP1 < HCVP1 And HighP2 < HCVP2 Then Range("C" & R1).Interior.Color = vbGreen
                                    
                                Next R1

 

[ging18]

Heres a starter on dynamic style arrays...

' Create the array with an undefined length
' The () indicate its an array
dim dblLow() as double

for intX = 1 to 10 step 1

   ' Resize the array. 
   ' Redim does the resizing.
   ' Preserve keeps the existing data (if you dont use
   '   preserve the array will be cleared each loop)
   redim preserve dblLow(ubound(dblLow) + 1)

   dblLow(ubound) = intX

next