Here is an excerpt of a macro that processes an array of street names
later in the macro. The street names can either come from a set group
of street names or a user inputted street or group of streets.

z = 0

If Worksheets("Macro").CheckBox1.Value = True Then
street = Array(""Columbine", "Lois", "Poppy")
Else:
Do Until yy = 7 ' 7=no
z = z + 1
ReDim Preserve street(1 To z)
street(z) = InputBox("Enter the name of the street you
want to search.")
yy = MsgBox("Would you like to enter another street?",
vbYesNo)
Loop
End If

The above construction fails. The following modification allows it
run, but seens unwieldy. Is there a more efficient method, one that
would allow me to use the array "street" in both branches of the IF
statement?

z = 0

If Worksheets("Macro").CheckBox1.Value = True Then

mystreet = Array("Columbine", "Lois", "Poppy")

For z = 0 To 2
ReDim Preserve street(1 To z + 1)
street(z + 1) = mystreet(z)
Next
Else:
Do Until yy = 7 '7=no
z = z + 1
ReDim Preserve street(1 To z)

street(z) = InputBox("Enter the name of the street you
want to search.")
yy = MsgBox("Would you like to enter another street and
city?", vbYesNo)
Loop
End If


....TIA, Ron

This worked ok for me...

Dim StreetArray As Variant
Dim resp As Long
Dim sCtr As Long

If Worksheets("Macro").CheckBox1.Value = True Then
StreetArray = Array("Columbine", "Lois", "Poppy")
Else
sCtr = 0
ReDim StreetArray(1 To 1)
Do Until resp = vbNo
sCtr = sCtr + 1
ReDim Preserve StreetArray(1 To sCtr)
StreetArray(sCtr) _
= InputBox("Enter the name of the street you want to search.")
resp _
= MsgBox(Prompt:="Would you like to enter another StreetArray?", _
Buttons:=vbYesNo)
Loop
End If

But I don't think I'd use it. If the user hits the cancel button on the first
pass through in that loop, then you'll still be including that empty string in
the array you're building.

I think I'd do something like:

Dim StreetArray As Variant
Dim StreetName As String
Dim TestWks As Worksheet
Dim resp As Long
Dim sCtr As Long
Dim StreetArrayCreated As Boolean

StreetArrayCreated = False

sCtr = -1 'an indicator
If Worksheets("Macro").CheckBox1.Value = True Then
StreetArray = Array("Columbine", "Lois", "Poppy")
StreetArrayCreated = True
Else
sCtr = 0
ReDim StreetArray(1 To 1)
resp = vbYes
Do
StreetName _
= InputBox("Enter the name of the StreetArray you want to search.")
If Trim(StreetName) <> "" Then
StreetArrayCreated = True
sCtr = sCtr + 1
ReDim Preserve StreetArray(1 To sCtr)
StreetArray(sCtr) = StreetName
End If
resp _
= MsgBox(Prompt:="Would you like to enter another StreetArray?", _
buttons:=vbYesNo)
Loop Until resp = vbNo
End If

If StreetArrayCreated = True Then
For sCtr = LBound(StreetArray) To UBound(StreetArray)
MsgBox StreetArray(sCtr)
Next sCtr
Else
MsgBox "no elements in StreetArray Array"
End If

I'm not sure what StreetArray (I changed the name) represents--but if it is a
collection of worksheet names, I'd add some more checking.

In fact, you may want to add a multiselect listbox to the worksheet that
displays all the possible streets so that you can just get the input that way.



ron wrote:
>
> Here is an excerpt of a macro that processes an array of street names
> later in the macro. The street names can either come from a set group
> of street names or a user inputted street or group of streets.
>
> z = 0
>
> If Worksheets("Macro").CheckBox1.Value = True Then
> street = Array(""Columbine", "Lois", "Poppy")
> Else:
> Do Until yy = 7 ' 7=no
> z = z + 1
> ReDim Preserve street(1 To z)
> street(z) = InputBox("Enter the name of the street you
> want to search.")
> yy = MsgBox("Would you like to enter another street?",
> vbYesNo)
> Loop
> End If
>
> The above construction fails. The following modification allows it
> run, but seens unwieldy. Is there a more efficient method, one that
> would allow me to use the array "street" in both branches of the IF
> statement?
>
> z = 0
>
> If Worksheets("Macro").CheckBox1.Value = True Then
>
> mystreet = Array("Columbine", "Lois", "Poppy")
>
> For z = 0 To 2
> ReDim Preserve street(1 To z + 1)
> street(z + 1) = mystreet(z)
> Next
> Else:
> Do Until yy = 7 '7=no
> z = z + 1
> ReDim Preserve street(1 To z)
>
> street(z) = InputBox("Enter the name of the street you
> want to search.")
> yy = MsgBox("Would you like to enter another street and
> city?", vbYesNo)
> Loop
> End If
>
> ...TIA, Ron

--

Dave Peterson

On Nov 18, 8:48*pm, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> This worked ok for me...
>
> * * Dim StreetArray As Variant
> * * Dim resp As Long
> * * Dim sCtr As Long
>
> * * If Worksheets("Macro").CheckBox1.Value = True Then
> * * * * StreetArray = Array("Columbine", "Lois", "Poppy")
> * * Else
> * * * * sCtr = 0
> * * * * ReDim StreetArray(1 To 1)
> * * * * Do Until resp = vbNo
> * * * * * * sCtr = sCtr + 1
> * * * * * * ReDim Preserve StreetArray(1 To sCtr)
> * * * * * * StreetArray(sCtr) _
> * * * * * * * = InputBox("Enter the name of the street you want to search.")
> * * * * * * resp _
> * * * * * * * = MsgBox(Prompt:="Would you like to enteranother StreetArray?", _
> * * * * * * * * * * * *Buttons:=vbYesNo)
> * * * * Loop
> * * End If
>
> But I don't think I'd use it. *If the user hits the cancel button on the first
> pass through in that loop, then you'll still be including that empty string in
> the array you're building.
>
> I think I'd do something like:
>
> * * Dim StreetArray As Variant
> * * Dim StreetName As String
> * * Dim TestWks As Worksheet
> * * Dim resp As Long
> * * Dim sCtr As Long
> * * Dim StreetArrayCreated As Boolean
>
> * * StreetArrayCreated = False
>
> * * sCtr = -1 'an indicator
> * * If Worksheets("Macro").CheckBox1.Value = True Then
> * * * * StreetArray = Array("Columbine", "Lois", "Poppy")
> * * * * StreetArrayCreated = True
> * * Else
> * * * * sCtr = 0
> * * * * ReDim StreetArray(1 To 1)
> * * * * resp = vbYes
> * * * * Do
> * * * * * * StreetName _
> * * * * * * *= InputBox("Enter the name of the StreetArray you want to search.")
> * * * * * * If Trim(StreetName) <> "" Then
> * * * * * * * * StreetArrayCreated = True
> * * * * * * * * sCtr = sCtr + 1
> * * * * * * * * ReDim Preserve StreetArray(1 To sCtr)
> * * * * * * * * StreetArray(sCtr) = StreetName
> * * * * * * End If
> * * * * * * resp _
> * * * * * * * = MsgBox(Prompt:="Would you like to enteranother StreetArray?", _
> * * * * * * * * * * * *buttons:=vbYesNo)
> * * * * Loop Until resp = vbNo
> * * End If
>
> * * If StreetArrayCreated = True Then
> * * * * For sCtr = LBound(StreetArray) To UBound(StreetArray)
> * * * * * * MsgBox StreetArray(sCtr)
> * * * * Next sCtr
> * * Else
> * * * * MsgBox "no elements in StreetArray Array"
> * * End If
>
> I'm not sure what StreetArray (I changed the name) represents--but if it is a
> collection of worksheet names, I'd add some more checking.
>
> In fact, you may want to add a multiselect listbox to the worksheet that
> displays all the possible streets so that you can just get the input thatway.
>
>
>
>
>
> ron wrote:
>
> > Here is an excerpt of a macro that processes an array of street names
> > later in the macro. *The street names can either come from a set group
> > of street names or a user inputted street or group of streets.
>
> > * * z = 0
>
> > * * If Worksheets("Macro").CheckBox1.Value = True Then
> > * * * * * * street = Array(""Columbine", "Lois", "Poppy")
> > * * Else:
> > * * * * Do Until yy = 7 * *' 7=no
> > * * * * * * z = z + 1
> > * * * * * * ReDim Preserve street(1 To z)
> > * * * * * * street(z) = InputBox("Enter the name of the street you
> > want to search.")
> > * * * * * * yy = MsgBox("Would you like to enter another street?",
> > vbYesNo)
> > * * * * Loop
> > * * End If
>
> > The above construction fails. *The following modification allows it
> > run, but seens unwieldy. *Is there a more efficient method, one that
> > would allow me to use the array "street" in both branches of the IF
> > statement?
>
> > * * z = 0
>
> > * * If Worksheets("Macro").CheckBox1.Value = True Then
>
> > * * * * * * mystreet = Array("Columbine", "Lois", "Poppy")
>
> > * * * * * * For z = 0 To 2
> > * * * * * * * *ReDim Preserve street(1 To z + 1)
> > * * * * * * * *street(z + 1) = mystreet(z)
> > * * * * * * Next
> > * * Else:
> > * * * * Do Until yy = 7 '7=no
> > * * * * * * z = z + 1
> > * * * * * * ReDim Preserve street(1 To z)
>
> > * * * * * * street(z) = InputBox("Enter the name of the street you
> > want to search.")
> > * * * * * * yy = MsgBox("Would you like to enter another street and
> > city?", vbYesNo)
> > * * * * Loop
> > * * End If
>
> > ...TIA, Ron
>
> --
>
> Dave Peterson- Hide quoted text -
>
> - Show quoted text -

Dave...Thanks for your response. It clarified things and enabled me
to find the error that was preventing me from using the same array in
both branches of the IF statement (changing my dim statement from
dim street() as string
to
dim street() as variant
solved the problem). Thanks again...Ron

One version of excel (xl97????) has a problem with:
dim street() as variant
I'd use:
dim street as variant
(without the ()'s)

ron wrote:
>
> On Nov 18, 8:48 pm, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> > This worked ok for me...
> >
> > Dim StreetArray As Variant
> > Dim resp As Long
> > Dim sCtr As Long
> >
> > If Worksheets("Macro").CheckBox1.Value = True Then
> > StreetArray = Array("Columbine", "Lois", "Poppy")
> > Else
> > sCtr = 0
> > ReDim StreetArray(1 To 1)
> > Do Until resp = vbNo
> > sCtr = sCtr + 1
> > ReDim Preserve StreetArray(1 To sCtr)
> > StreetArray(sCtr) _
> > = InputBox("Enter the name of the street you want to search.")
> > resp _
> > = MsgBox(Prompt:="Would you like to enter another StreetArray?", _
> > Buttons:=vbYesNo)
> > Loop
> > End If
> >
> > But I don't think I'd use it. If the user hits the cancel button on the first
> > pass through in that loop, then you'll still be including that empty string in
> > the array you're building.
> >
> > I think I'd do something like:
> >
> > Dim StreetArray As Variant
> > Dim StreetName As String
> > Dim TestWks As Worksheet
> > Dim resp As Long
> > Dim sCtr As Long
> > Dim StreetArrayCreated As Boolean
> >
> > StreetArrayCreated = False
> >
> > sCtr = -1 'an indicator
> > If Worksheets("Macro").CheckBox1.Value = True Then
> > StreetArray = Array("Columbine", "Lois", "Poppy")
> > StreetArrayCreated = True
> > Else
> > sCtr = 0
> > ReDim StreetArray(1 To 1)
> > resp = vbYes
> > Do
> > StreetName _
> > = InputBox("Enter the name of the StreetArray you want to search.")
> > If Trim(StreetName) <> "" Then
> > StreetArrayCreated = True
> > sCtr = sCtr + 1
> > ReDim Preserve StreetArray(1 To sCtr)
> > StreetArray(sCtr) = StreetName
> > End If
> > resp _
> > = MsgBox(Prompt:="Would you like to enter another StreetArray?", _
> > buttons:=vbYesNo)
> > Loop Until resp = vbNo
> > End If
> >
> > If StreetArrayCreated = True Then
> > For sCtr = LBound(StreetArray) To UBound(StreetArray)
> > MsgBox StreetArray(sCtr)
> > Next sCtr
> > Else
> > MsgBox "no elements in StreetArray Array"
> > End If
> >
> > I'm not sure what StreetArray (I changed the name) represents--but if it is a
> > collection of worksheet names, I'd add some more checking.
> >
> > In fact, you may want to add a multiselect listbox to the worksheet that
> > displays all the possible streets so that you can just get the input that way.
> >
> >
> >
> >
> >
> > ron wrote:
> >
> > > Here is an excerpt of a macro that processes an array of street names
> > > later in the macro. The street names can either come from a set group
> > > of street names or a user inputted street or group of streets.
> >
> > > z = 0
> >
> > > If Worksheets("Macro").CheckBox1.Value = True Then
> > > street = Array(""Columbine", "Lois", "Poppy")
> > > Else:
> > > Do Until yy = 7 ' 7=no
> > > z = z + 1
> > > ReDim Preserve street(1 To z)
> > > street(z) = InputBox("Enter the name of the street you
> > > want to search.")
> > > yy = MsgBox("Would you like to enter another street?",
> > > vbYesNo)
> > > Loop
> > > End If
> >
> > > The above construction fails. The following modification allows it
> > > run, but seens unwieldy. Is there a more efficient method, one that
> > > would allow me to use the array "street" in both branches of the IF
> > > statement?
> >
> > > z = 0
> >
> > > If Worksheets("Macro").CheckBox1.Value = True Then
> >
> > > mystreet = Array("Columbine", "Lois", "Poppy")
> >
> > > For z = 0 To 2
> > > ReDim Preserve street(1 To z + 1)
> > > street(z + 1) = mystreet(z)
> > > Next
> > > Else:
> > > Do Until yy = 7 '7=no
> > > z = z + 1
> > > ReDim Preserve street(1 To z)
> >
> > > street(z) = InputBox("Enter the name of the street you
> > > want to search.")
> > > yy = MsgBox("Would you like to enter another street and
> > > city?", vbYesNo)
> > > Loop
> > > End If
> >
> > > ...TIA, Ron
> >
> > --
> >
> > Dave Peterson- Hide quoted text -
> >
> > - Show quoted text -
>
> Dave...Thanks for your response. It clarified things and enabled me
> to find the error that was preventing me from using the same array in
> both branches of the IF statement (changing my dim statement from
> dim street() as string
> to
> dim street() as variant
> solved the problem). Thanks again...Ron

--

Dave Peterson

ps. The real problem is a combination of:

Dim street() as variant
street = array(....)

It's not just the declaration line that's a problem.

Dave Peterson wrote:
>
> One version of excel (xl97????) has a problem with:
> dim street() as variant
> I'd use:
> dim street as variant
> (without the ()'s)
>
> ron wrote:
> >
> > On Nov 18, 8:48 pm, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> > > This worked ok for me...
> > >
> > > Dim StreetArray As Variant
> > > Dim resp As Long
> > > Dim sCtr As Long
> > >
> > > If Worksheets("Macro").CheckBox1.Value = True Then
> > > StreetArray = Array("Columbine", "Lois", "Poppy")
> > > Else
> > > sCtr = 0
> > > ReDim StreetArray(1 To 1)
> > > Do Until resp = vbNo
> > > sCtr = sCtr + 1
> > > ReDim Preserve StreetArray(1 To sCtr)
> > > StreetArray(sCtr) _
> > > = InputBox("Enter the name of the street you want to search.")
> > > resp _
> > > = MsgBox(Prompt:="Would you like to enter another StreetArray?", _
> > > Buttons:=vbYesNo)
> > > Loop
> > > End If
> > >
> > > But I don't think I'd use it. If the user hits the cancel button on the first
> > > pass through in that loop, then you'll still be including that empty string in
> > > the array you're building.
> > >
> > > I think I'd do something like:
> > >
> > > Dim StreetArray As Variant
> > > Dim StreetName As String
> > > Dim TestWks As Worksheet
> > > Dim resp As Long
> > > Dim sCtr As Long
> > > Dim StreetArrayCreated As Boolean
> > >
> > > StreetArrayCreated = False
> > >
> > > sCtr = -1 'an indicator
> > > If Worksheets("Macro").CheckBox1.Value = True Then
> > > StreetArray = Array("Columbine", "Lois", "Poppy")
> > > StreetArrayCreated = True
> > > Else
> > > sCtr = 0
> > > ReDim StreetArray(1 To 1)
> > > resp = vbYes
> > > Do
> > > StreetName _
> > > = InputBox("Enter the name of the StreetArray you want to search.")
> > > If Trim(StreetName) <> "" Then
> > > StreetArrayCreated = True
> > > sCtr = sCtr + 1
> > > ReDim Preserve StreetArray(1 To sCtr)
> > > StreetArray(sCtr) = StreetName
> > > End If
> > > resp _
> > > = MsgBox(Prompt:="Would you like to enter another StreetArray?", _
> > > buttons:=vbYesNo)
> > > Loop Until resp = vbNo
> > > End If
> > >
> > > If StreetArrayCreated = True Then
> > > For sCtr = LBound(StreetArray) To UBound(StreetArray)
> > > MsgBox StreetArray(sCtr)
> > > Next sCtr
> > > Else
> > > MsgBox "no elements in StreetArray Array"
> > > End If
> > >
> > > I'm not sure what StreetArray (I changed the name) represents--but if it is a
> > > collection of worksheet names, I'd add some more checking.
> > >
> > > In fact, you may want to add a multiselect listbox to the worksheet that
> > > displays all the possible streets so that you can just get the input that way.
> > >
> > >
> > >
> > >
> > >
> > > ron wrote:
> > >
> > > > Here is an excerpt of a macro that processes an array of street names
> > > > later in the macro. The street names can either come from a set group
> > > > of street names or a user inputted street or group of streets.
> > >
> > > > z = 0
> > >
> > > > If Worksheets("Macro").CheckBox1.Value = True Then
> > > > street = Array(""Columbine", "Lois", "Poppy")
> > > > Else:
> > > > Do Until yy = 7 ' 7=no
> > > > z = z + 1
> > > > ReDim Preserve street(1 To z)
> > > > street(z) = InputBox("Enter the name of the street you
> > > > want to search.")
> > > > yy = MsgBox("Would you like to enter another street?",
> > > > vbYesNo)
> > > > Loop
> > > > End If
> > >
> > > > The above construction fails. The following modification allows it
> > > > run, but seens unwieldy. Is there a more efficient method, one that
> > > > would allow me to use the array "street" in both branches of the IF
> > > > statement?
> > >
> > > > z = 0
> > >
> > > > If Worksheets("Macro").CheckBox1.Value = True Then
> > >
> > > > mystreet = Array("Columbine", "Lois", "Poppy")
> > >
> > > > For z = 0 To 2
> > > > ReDim Preserve street(1 To z + 1)
> > > > street(z + 1) = mystreet(z)
> > > > Next
> > > > Else:
> > > > Do Until yy = 7 '7=no
> > > > z = z + 1
> > > > ReDim Preserve street(1 To z)
> > >
> > > > street(z) = InputBox("Enter the name of the street you
> > > > want to search.")
> > > > yy = MsgBox("Would you like to enter another street and
> > > > city?", vbYesNo)
> > > > Loop
> > > > End If
> > >
> > > > ...TIA, Ron
> > >
> > > --
> > >
> > > Dave Peterson- Hide quoted text -
> > >
> > > - Show quoted text -
> >
> > Dave...Thanks for your response. It clarified things and enabled me
> > to find the error that was preventing me from using the same array in
> > both branches of the IF statement (changing my dim statement from
> > dim street() as string
> > to
> > dim street() as variant
> > solved the problem). Thanks again...Ron
>
> --
>
> Dave Peterson

--

Dave Peterson