View Full Version : [SOLVED:] What does this error mean
Djblois
07-26-2006, 08:19 AM
In my code I am getting this error. "For each may only iterate over a collection object or an array" Here is the code I am getting this error for
Dim stk as boolean
Dim startToKeep as string
StartToKeep = Array("AT", "CRN", "Customer", "Product")
Rows(1).Insert
Cells(1, 1) = "Sacrifice"
For Each stk In StartToKeep
Cells.AutoFilter Field:=1, Criteria1:=stk & "*"
Intersect(ActiveSheet.UsedRange, _
Columns(1).SpecialCells(xlCellTypeVisible)).Offset(, 18) = "x"
Cells.AutoFilter
Next
Killian
07-26-2006, 08:33 AM
Dim stk As Boolean
Dim startToKeep As String
Your declarations mean that your statement is compiled as:
"For Each Boolean In a String"
Hence the error
Declaring them both as Variant should fix it
Or you can loop through the array by index
For i = LBound(startToKeep) To UBound(startToKeep)
Djblois
07-26-2006, 08:51 AM
Please explain what you mean by declare them both as variants? How are they declared now? Also, what do you mean by loop through array by index? I am trying to learn as I go, I am a beginner and I want to get better.
compariniaa
07-26-2006, 10:16 AM
stk is declared as Boolean and starttokeep is declared as a string, so change both "boolean" and "string" to "variant"
Djblois
07-26-2006, 11:19 AM
Ok compariniaa that worked but what is a variant??? I know what a string is, a long is, a boolean, etc... but what is a variant? when is it used?
Daniel
compariniaa
07-26-2006, 12:23 PM
from my understanding, "variant" means excel determines what type of variable it will be...this is good because it can change each time, so it's flexible, but it's bad because it slows your code down since excel has to determine the data type
that being said....it doesn't really matter when you use it (as far as i know)
compariniaa
07-26-2006, 12:29 PM
Also, what do you mean by loop through array by index?
when you refer to an individual item in an array (e.g. item 2 would be referred to as yourarray(2)), the reference number is the index, so in the example i used, the index number for the second item would be 2 (assuming you're using option base 1), so looping through an array by index is just going through all the items in the array using a variable
in Killian's example, he would tell the code to look at item with the index number "i", then he'd add one to "i" so that when the code looped back to it the code would look at the next item in the array
jindon
07-26-2006, 09:28 PM
when you refer to an individual item in an array (e.g. item 2 would be referred to as yourarray(2)), the reference number is the index, so in the example i used, the index number for the second item would be 2 (assuming you're using option base 1), so looping through an array by index is just going through all the items in the array using a variable
in Killian's example, he would tell the code to look at item with the index number "i", then he'd add one to "i" so that when the code looped back to it the code would look at the next item in the array
Even if you state Option Base 1, the array produced by the function like
Array
Split
Filter
Lower Bound is always 0....
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.