R
ron
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
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