What does this error mean

[Djblois]

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]

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]

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]

stk is declared as Boolean and starttokeep is declared as a string, so change both "boolean" and "string" to "variant"

[Djblois]

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]

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]

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]

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....