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)

 

[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

 

[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)

 

[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!