Macro for user to input 10 numbers

[Deedee]

Hi, I'm a bit of a novice when it comes to macros and hoping someone can
help. I need a user to be prompted (by a pop up box) to enter 10 numbers
between 1-100, there are no duplicates allowed. these 10 numbers then need
to be sorted and arranged in ascending order and displayed in cells B14:J14.

I've managed to get an input box for one number but really struggling with
the rest. Can anyone help?

[Bob Phillips]

Sub numbers()
Dim i As Long
Dim n

For i = 2 To 10
With Cells(14, i)
Do
n = Application.InputBox("Input number #" & i - 1, Type:=3)
If n = False Then
Exit Sub
End If
Loop Until IsNumeric(n) And
Application.CountIf(Range("B14:J14"), n) = 0
Cells(14, i).Value = n
End With
Next i

End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Deedee" <[email protected]> wrote in message
news:[email protected]...
> Hi, I'm a bit of a novice when it comes to macros and hoping someone can
> help. I need a user to be prompted (by a pop up box) to enter 10 numbers
> between 1-100, there are no duplicates allowed. these 10 numbers then
need
> to be sorted and arranged in ascending order and displayed in cells
B14:J14.
>
> I've managed to get an input box for one number but really struggling with
> the rest. Can anyone help?

[Neil]

The code between the '=" signs does what you want:

======================
Range("B14").Select
Dim x As Integer
Dim y As Integer

For x = 1 To 10
y = InputBox("Enter Number")
ActiveCell = y
ActiveCell.Offset(0, 1).Select
Next x

Range("b14", "k14").Select

Selection.Sort Key1:=Range("B14"), Order1:=xlAscending,
Header:=xlGuess,_ OrderCustom:=1, MatchCase:=False,
Orientation:=xlLeftToRight,_ DataOption1:=xlSortNormal

======================

Note that the last three lines are actually just one long line wrapped, but
if you copy and paste it into the VBA editor then it will work ok.

Also be aware that there's no data validation etc in this so it's possible
to enter incorrect informtion, so you will have to add that part. (If you're
struggling with that then post back and we'll have a look)

HTH

Neil
www.nwarwick.co.uk
"Deedee" wrote:

> Hi, I'm a bit of a novice when it comes to macros and hoping someone can
> help. I need a user to be prompted (by a pop up box) to enter 10 numbers
> between 1-100, there are no duplicates allowed. these 10 numbers then need
> to be sorted and arranged in ascending order and displayed in cells B14:J14.
>
> I've managed to get an input box for one number but really struggling with
> the rest. Can anyone help?

[Bob Phillips]

Revision as I missed the sort and the range check

Sub numbers()
Dim i As Long
Dim n

For i = 2 To 10
With Cells(14, i)
Do
n = Application.InputBox("Input number # between 1 and 100"
& i - 1, Type:=3)
If n = False Then
Exit Sub
End If
Loop Until IsNumeric(n) And _
n > 0 And n <= 100 And _
Application.CountIf(Range("B14:J14"), n) = 0
Cells(14, i).Value = n
End With
Next i

Range("B14:J14").Sort Key1:=Range("B14"), _
Order1:=xlAscending, _
Header:=xlNo, _
Orientation:=xlLeftToRight

End Sub

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Bob Phillips" <[email protected]> wrote in message
news:[email protected]...
> Sub numbers()
> Dim i As Long
> Dim n
>
> For i = 2 To 10
> With Cells(14, i)
> Do
> n = Application.InputBox("Input number #" & i - 1,
Type:=3)
> If n = False Then
> Exit Sub
> End If
> Loop Until IsNumeric(n) And
> Application.CountIf(Range("B14:J14"), n) = 0
> Cells(14, i).Value = n
> End With
> Next i
>
> End Sub
>
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Deedee" <[email protected]> wrote in message
> news:[email protected]...
> > Hi, I'm a bit of a novice when it comes to macros and hoping someone can
> > help. I need a user to be prompted (by a pop up box) to enter 10
numbers
> > between 1-100, there are no duplicates allowed. these 10 numbers then
> need
> > to be sorted and arranged in ascending order and displayed in cells
> B14:J14.
> >
> > I've managed to get an input box for one number but really struggling
with
> > the rest. Can anyone help?
>
>

[Deedee]

Thanks Guys!!! Thats worked just the way I need it! Looking at the code it
looks pretty simple but I just couldn't get my head around it!

"Bob Phillips" wrote:

> Revision as I missed the sort and the range check
>
> Sub numbers()
> Dim i As Long
> Dim n
>
> For i = 2 To 10
> With Cells(14, i)
> Do
> n = Application.InputBox("Input number # between 1 and 100"
> & i - 1, Type:=3)
> If n = False Then
> Exit Sub
> End If
> Loop Until IsNumeric(n) And _
> n > 0 And n <= 100 And _
> Application.CountIf(Range("B14:J14"), n) = 0
> Cells(14, i).Value = n
> End With
> Next i
>
> Range("B14:J14").Sort Key1:=Range("B14"), _
> Order1:=xlAscending, _
> Header:=xlNo, _
> Orientation:=xlLeftToRight
>
> End Sub
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Bob Phillips" <[email protected]> wrote in message
> news:[email protected]...
> > Sub numbers()
> > Dim i As Long
> > Dim n
> >
> > For i = 2 To 10
> > With Cells(14, i)
> > Do
> > n = Application.InputBox("Input number #" & i - 1,
> Type:=3)
> > If n = False Then
> > Exit Sub
> > End If
> > Loop Until IsNumeric(n) And
> > Application.CountIf(Range("B14:J14"), n) = 0
> > Cells(14, i).Value = n
> > End With
> > Next i
> >
> > End Sub
> >
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "Deedee" <[email protected]> wrote in message
> > news:[email protected]...
> > > Hi, I'm a bit of a novice when it comes to macros and hoping someone can
> > > help. I need a user to be prompted (by a pop up box) to enter 10
> numbers
> > > between 1-100, there are no duplicates allowed. these 10 numbers then
> > need
> > > to be sorted and arranged in ascending order and displayed in cells
> > B14:J14.
> > >
> > > I've managed to get an input box for one number but really struggling
> with
> > > the rest. Can anyone help?
> >
> >
>
>
>