help with array

  • Thread starter Thread starter bobh
  • Start date Start date
B

bobh

Hi All,

In Excel 2003 I'm trying to make a variable an array and store the
values entered by a user using an inputbox and I'm failing badly.....
below is what I tried but it does not work so how do I do that?
thanks
bobh.

'input looks like this --> 3,8,19,21,36
Dim PRange() As Integer
PRange() = InputBox("Enter up to 5 page nbr's to print, seperated
by a comma( , )", "Enter Page Nbrs")
 
I don't use 2003, but this worked for me in 2010:

Dim PRange() As Variant
PRange() = Application.InputBox("Enter up to 5 page nbr's to print, " & _
"using this format:" & vbCr & vbCr & _
"{Number_1, Number_2, etc.}", "Enter Page Nbrs", "{3,8,19,21,36}", , , , , 64)
'Below line prints inputbox results to Sheet1 row 1
Sheet1.Range("A1:E1").Value = PRange

Ben
 
Try...

Dim vPageNums As Variant, sPageNums As String, n As Long

'Get the values...
sPageNums = InputBox("Enter up to 5 page nbr's to print, seperated
by a comma(ie: 1,3,5,7,9)", "Enter Page Nbrs")

'Create the array...
vpageNums = Split(sPageNums, ",")

'Do stuff...
For n = LBound(vPageNums) To UBound(vPageNums)
Debug.Print vPageNums(n)
Next 'n

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
I don't use 2003, but this worked for me in 2010:

Dim PRange() As Variant
   PRange() = Application.InputBox("Enter up to 5 page nbr's to print, " & _
    "using this format:" & vbCr & vbCr & _
    "{Number_1, Number_2, etc.}", "Enter Page Nbrs", "{3,8,19,21,36}", , , , , 64)
'Below line prints inputbox results to Sheet1 row 1
    Sheet1.Range("A1:E1").Value = PRange

Ben
Click to expand...

thanks for your reply, it works :) , looking to resolve having to
type the { } in the inputbox
 
Try...

  Dim vPageNums As Variant, sPageNums As String, n As Long

  'Get the values...
  sPageNums = InputBox("Enter up to 5 page nbr's to print, seperated
by a comma(ie: 1,3,5,7,9)", "Enter Page Nbrs")

  'Create the array...
  vpageNums = Split(sPageNums, ",")

  'Do stuff...
  For n = LBound(vPageNums) To UBound(vPageNums)
    Debug.Print vPageNums(n)
  Next 'n

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup!
    comp.lang.basic.visual.misc
    microsoft.public.vb.general.discussion
Click to expand...

Thanks, it works great :) it is what I was hoping to find. Here's
what I ended up with in case anyone else is looking for something
like this


Sub PrintPages()
'
Dim vPgNums As Variant, sPgNums As String, n As Long

'Get the values from the user
sPgNums = InputBox("Enter up to 5 page nbr's to print, seperated by
a comma(ie: 1,5,9,14,21)", "Enter Page Nbrs")

'check to be sure the user entered something
If Len(sPgNums & "") = 0 Then
Exit Sub
Else
'create an array of the values entered
vPgNums = Split(sPgNums, ",")

'get each page nbr and print it
For n = LBound(vPgNums) To UBound(vPgNums)
ActiveWindow.SelectedSheets.PrintOut From:=vPgNums(n),
To:=vPgNums(n), Copies:=1
Next 'n
End If

End Sub
 
Here's my (commented) approach to your solution:
Sub PrintPages()
'
Dim vPgNums As Variant, sPgNums As String, n As Long

'Get the values from the user
sPgNums = InputBox("Enter up to 5 page nbr's to print, seperated by
a comma(ie: 1,5,9,14,21)", "Enter Page Nbrs")
Click to expand...
''''''''''''''''''''''''''''''''''''''''''''
'This block isn't necessary because printout
'will only happen if user entered page numbers.

'**Also, I don't get why you add an empty
'string to the variable before testing its length**
' If Len(sPgNums & "") = 0 Then
' Exit Sub
' Else
''''''''''''''''''''''''''''''''''''''''''''
'Make sure user didn't cancel
If Len(sPgNums) > 0 Then
'create an array of the values entered
vPgNums = Split(sPgNums, ",")

'get each page nbr and print it
For n = LBound(vPgNums) To UBound(vPgNums)
ActiveWindow.SelectedSheets.PrintOut From:=vPgNums(n),
To:=vPgNums(n), Copies:=1
Next 'n
End If

End Sub
Click to expand...

Note that you could also pass sPgNums as the 'Pages' parameter

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
Back
Top