Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Clearing values automaticaly when different function selected | New Users to Excel | |||
Clearing cells without clearing formulas | Excel Discussion (Misc queries) | |||
How to use values stored in a combo box control? | Excel Discussion (Misc queries) | |||
Array: Counting multiple values within array | Excel Worksheet Functions | |||
Clearing the Values in a Drop - Down List | Excel Discussion (Misc queries) |