vba clearing out values stored in array
 

I have an array in a loop.
The first pass thru the loop it collects values and stores them in the
array, if i have conditions set for the second pass thru the loop and
the array doesnt reset(so to speak) and no values are to be stored, i
find that the array still carries all the old values from the first
pass.

My question is, how can i make sure my array is empty before it goes
thru the loop again??
here is a bit of what i have for code: the array varies in size each
time.

Set rng = Range("D" & J + K, "N" & J + K)
count = 1

For Each c In rng


If (Not IsEmpty(c) And IsNumeric(c)) Then
Select Case c.Font.ColorIndex
Case xlAutomatic, 1
ReDim Preserve meanArray(1 To count)

meanArray(count) = c.Value
count = count + 1
End Select

thanks alot for everyones help.


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements

vba clearing out values stored in array
 

Set rng = Range("D" & J + K, "N" & J + K)
count = 1

' since you know the max number of elements, do a redim with
' no preserve
ReDim meanArray(1 to rng.Count)
For Each c In rng
If (Not IsEmpty(c) And IsNumeric(c)) Then
Select Case c.Font.ColorIndex
Case xlAutomatic, 1
meanArray(count) = c.Value
count = count + 1
End Select
end if
Next

Redim Preserve meanArray(1 to count-1)



--
Regards,
Tom Ogilvy

"chick-racer" wrote in message
...

I have an array in a loop.
The first pass thru the loop it collects values and stores them in the
array, if i have conditions set for the second pass thru the loop and
the array doesnt reset(so to speak) and no values are to be stored, i
find that the array still carries all the old values from the first
pass.

My question is, how can i make sure my array is empty before it goes
thru the loop again??
here is a bit of what i have for code: the array varies in size each
time.

Set rng = Range("D" & J + K, "N" & J + K)
count = 1

For Each c In rng


If (Not IsEmpty(c) And IsNumeric(c)) Then
Select Case c.Font.ColorIndex
Case xlAutomatic, 1
ReDim Preserve meanArray(1 To count)

meanArray(count) = c.Value
count = count + 1
End Select

thanks alot for everyones help.


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

~~Now Available: Financial Statements.xls, a step by step guide to
creating financial statements

vba clearing out values stored in array
 

Also look in Excel VBA help at ERASE.

--
Regards,
Tom Ogilvy

"Tom Ogilvy" wrote in message
...
Set rng = Range("D" & J + K, "N" & J + K)
count = 1

' since you know the max number of elements, do a redim with
' no preserve
ReDim meanArray(1 to rng.Count)
For Each c In rng
If (Not IsEmpty(c) And IsNumeric(c)) Then
Select Case c.Font.ColorIndex
Case xlAutomatic, 1
meanArray(count) = c.Value
count = count + 1
End Select
end if
Next

Redim Preserve meanArray(1 to count-1)



--
Regards,
Tom Ogilvy

"chick-racer" wrote in message
...

I have an array in a loop.
The first pass thru the loop it collects values and stores them in the
array, if i have conditions set for the second pass thru the loop and
the array doesnt reset(so to speak) and no values are to be stored, i
find that the array still carries all the old values from the first
pass.

My question is, how can i make sure my array is empty before it goes
thru the loop again??
here is a bit of what i have for code: the array varies in size each
time.

Set rng = Range("D" & J + K, "N" & J + K)
count = 1

For Each c In rng


If (Not IsEmpty(c) And IsNumeric(c)) Then
Select Case c.Font.ColorIndex
Case xlAutomatic, 1
ReDim Preserve meanArray(1 To count)

meanArray(count) = c.Value
count = count + 1
End Select

thanks alot for everyones help.


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

~~Now Available: Financial Statements.xls, a step by step guide to
creating financial statements