Hello all

I have a procedure which creates an array based upon values in a named
range. There's three possible ranges which can be selected. The first
two ranges contain more than one value, whereas my third only contains
one item.

I create the array as follows. (The gstrReportingArea variable relates
to one of the three possible ranges, and mwshtVariablesConstants is a
worksheet object variable).

Public gaFactoryAreas() As String
'Create variable array to hold factory areas
c = mwshtVariablesConstants.Range("var" & gstrReportingArea &
"_FactoryAreas").Columns.Count
ReDim gaFactoryAreas(1 To c)
For i = 1 To c
gaFactoryAreas(i) = mwshtVariablesConstants.Range("var" &
gstrReportingArea & "_FactoryAreas").Cells(1, i)
Next i

My problem relates to when I try to use gaFactoryAreas array in a 'For
Each' loop.

When I have only one item in gaFactoryAreas, the loop fails, whereas if
there's more than one item it fails.

Does anyone have any suggestions on how to deal with the possibility
that there may only be one item in an array. I'"m trying to avoid using
lots of IF statements.

Regards

Exuse me I just noticed the typo. I wanted to say...

When I have only one item in gaFactoryAreas, the loop fails, whereas if

there's more than one item, it **WORKS**

read up on array

unless otherwise instructed array start at zero not 1

check your for each loops

try

For i = LBound(gaFactoryAreas) To UBound(gaFactoryAreas)
' do something with Arr(gaFactoryAreas)
Next i


"mo_in_france" wrote:

Hello all

I have a procedure which creates an array based upon values in a named
range. There's three possible ranges which can be selected. The first
two ranges contain more than one value, whereas my third only contains
one item.

I create the array as follows. (The gstrReportingArea variable relates
to one of the three possible ranges, and mwshtVariablesConstants is a
worksheet object variable).

Public gaFactoryAreas() As String
'Create variable array to hold factory areas
c = mwshtVariablesConstants.Range("var" & gstrReportingArea &
"_FactoryAreas").Columns.Count
ReDim gaFactoryAreas(1 To c)
For i = 1 To c
gaFactoryAreas(i) = mwshtVariablesConstants.Range("var" &
gstrReportingArea & "_FactoryAreas").Cells(1, i)
Next i

My problem relates to when I try to use gaFactoryAreas array in a 'For
Each' loop.

When I have only one item in gaFactoryAreas, the loop fails, whereas if
there's more than one item it fails.

Does anyone have any suggestions on how to deal with the possibility
that there may only be one item in an array. I'"m trying to avoid using
lots of IF statements.

Regards

Public gaFactoryAreas() As String
Sub testme()

'Create variable array to hold factory areas
c = 1
ReDim gaFactoryAreas(1 To c)
For i = 1 To c
Debug.Print i

Next i

End Sub

works fine for me.

--
Regards,
Tom Ogilvy

"mo_in_france" wrote in message
ups.com...
Exuse me I just noticed the typo. I wanted to say...

When I have only one item in gaFactoryAreas, the loop fails, whereas if

there's more than one item, it **WORKS**

Couldn't you just loop through the elements of the array the same way you built
it?

dim j as long
for j = lbound(gafactoryareas) to ubound(gafactoryareas)
msgbox gafactoryareas(j)
next j



mo_in_france wrote:

Hello all

I have a procedure which creates an array based upon values in a named
range. There's three possible ranges which can be selected. The first
two ranges contain more than one value, whereas my third only contains
one item.

I create the array as follows. (The gstrReportingArea variable relates
to one of the three possible ranges, and mwshtVariablesConstants is a
worksheet object variable).

Public gaFactoryAreas() As String
'Create variable array to hold factory areas
c = mwshtVariablesConstants.Range("var" & gstrReportingArea &
"_FactoryAreas").Columns.Count
ReDim gaFactoryAreas(1 To c)
For i = 1 To c
gaFactoryAreas(i) = mwshtVariablesConstants.Range("var" &
gstrReportingArea & "_FactoryAreas").Cells(1, i)
Next i

My problem relates to when I try to use gaFactoryAreas array in a 'For
Each' loop.

When I have only one item in gaFactoryAreas, the loop fails, whereas if
there's more than one item it fails.

Does anyone have any suggestions on how to deal with the possibility
that there may only be one item in an array. I'"m trying to avoid using
lots of IF statements.

Regards

--

Dave Peterson

I think he otherwise instructed:

ReDim gaFactoryAreas(1 To c)


--
Regards,
Tom Ogilvy


"Vacation's Over" wrote in message
...
read up on array

unless otherwise instructed array start at zero not 1

check your for each loops

try

For i = LBound(gaFactoryAreas) To UBound(gaFactoryAreas)
' do something with Arr(gaFactoryAreas)
Next i


"mo_in_france" wrote:

Hello all

I have a procedure which creates an array based upon values in a named
range. There's three possible ranges which can be selected. The first
two ranges contain more than one value, whereas my third only contains
one item.

I create the array as follows. (The gstrReportingArea variable relates
to one of the three possible ranges, and mwshtVariablesConstants is a
worksheet object variable).

Public gaFactoryAreas() As String
'Create variable array to hold factory areas
c = mwshtVariablesConstants.Range("var" & gstrReportingArea &
"_FactoryAreas").Columns.Count
ReDim gaFactoryAreas(1 To c)
For i = 1 To c
gaFactoryAreas(i) = mwshtVariablesConstants.Range("var" &
gstrReportingArea & "_FactoryAreas").Cells(1, i)
Next i

My problem relates to when I try to use gaFactoryAreas array in a 'For
Each' loop.

When I have only one item in gaFactoryAreas, the loop fails, whereas if
there's more than one item it fails.

Does anyone have any suggestions on how to deal with the possibility
that there may only be one item in an array. I'"m trying to avoid using
lots of IF statements.

Regards