Macro for user to input 10 numbers

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?
0
deedee (6)
10/21/2005 1:27:05 PM
excel.newusers 15348 articles. 2 followers. Follow

4 Replies
346 Views

Similar Articles

[PageSpeed] 9

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" <Deedee@discussions.microsoft.com> wrote in message
news:60A87D17-BDA8-4EDA-B80C-06DA1D70D23B@microsoft.com...
> 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?


0
bob.phillips1 (6510)
10/21/2005 1:48:20 PM
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?
0
neil7418 (200)
10/21/2005 1:50:04 PM
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" <bob.phillips@notheretiscali.co.uk> wrote in message
news:u$j2cYk1FHA.2880@TK2MSFTNGP12.phx.gbl...
> 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" <Deedee@discussions.microsoft.com> wrote in message
> news:60A87D17-BDA8-4EDA-B80C-06DA1D70D23B@microsoft.com...
> > 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?
>
>


0
bob.phillips1 (6510)
10/21/2005 2:53:53 PM
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" <bob.phillips@notheretiscali.co.uk> wrote in message
> news:u$j2cYk1FHA.2880@TK2MSFTNGP12.phx.gbl...
> > 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" <Deedee@discussions.microsoft.com> wrote in message
> > news:60A87D17-BDA8-4EDA-B80C-06DA1D70D23B@microsoft.com...
> > > 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?
> >
> >
> 
> 
> 
0
deedee (6)
10/21/2005 6:44:05 PM
Reply:

Similar Artilces:

Formatting a subtotal line with a macro
I already have in use a macro that runs a sort and subtutal function. I would like to format the subtotal row using visual basic since there is already a macro in place. Hi Look at this: ActiveCell.Font.Bold = True Regards, Per "DJDKAL" <daniel.kalfayan@hotmail.com> skrev i meddelelsen news:A86040B0-B4C4-4E11-A80C-E80132C9710F@microsoft.com... >I already have in use a macro that runs a sort and subtutal function. I > would like to format the subtotal row using visual basic since there is > already a macro in place. This is what I've g...

Memory Leak #10
I added to the main MFC dll class sterilization support. In the InitInstance I added the following (and a corresponding code in the ExitInstance). It seems to work fine but I get memory leak on the program shutdown. Can someone advise please? Gilai .... CFile theFile; theFile.Open("Dllpcap.dat", CFile::modeRead); CArchive archive(&theFile, CArchive::load, 4096); Serialize(archive); archive.Close(); theFile.Close(); Do you know what/where the memory leak is happening. There are some things in MFC that just get allocated once and perhaps they just never clea...

users with restricted access
We have some users that we have directed to only get their information from a report that has been set up. Because of that, I set up a parameter query to make the information more easy to see. The parameter query prompts for last name or broker #, is there a way, when the last name is entered to include Jr's & Sr's? Or should this be another field in the table to make the last name field more accurate? ...

How to make Run Integration work in GP 10
Can anyone tell me how to make the Run Integration tool work in GP 10. We just did our first client upgrade to GP 10 and the client has only 1 integration that they used to be able to run via the RUN INTEGRATION Tool. Any help would be greatly appreciated. -- Tammy Chavez I have the same probelm...did you get any answers to this? I have to go into Integration Manager to run it b/c Tools>Run Integration does nothing. I even checked the ini files to make sure the path is correct. "Tammy Chavez" wrote: > Can anyone tell me how to make the Run Integration tool work in...

Office 2007 forms
I am creating a form with office 2007, will those people who do not use office 2007 be able to fill in my form? should I save it in a particular format? thanks Provided you start from the normal template, don't use fonts that were introduced with Word 2007, and save the form in Word 97-2003 document format, anyone with Word 97 or later should be able to open it. Use only the legacy form fields, to which end http://gregmaxey.mvps.org/Classic%20Form%20Controls.htm will make things easier. -- <>>< ><<> ><<> <>>< ><<...

VB Macro #2
I have a macro that is generating Excel files. When the macro runs, it saves two different tabs in the workbooks as values. It needs to test and wait until the formulas that are pulling amounts from another database are correctly populated with the amounts. Right now, I am getting N/A# instead of amounts for some, but not all, of the files. I think the problem is that the formulas have not completed pulling the amounts before the macro pastes that tab as zeros. (N/A# is what appears when the formulas have not completed calculating - once they have completed, the N/A# is replaced ...

SBS 2003 moving of users files
I run SBS 2003 and due to the amount of data on the users drive it has become chokers and have installed a new 1tb drive to keep up with demand for space. I need to move all the data to the new drive but unsure of the process. Is there an easy way of doing this? As it needs to be done asap Thanks -- JimmyJames ------------------------------------------------------------------------ JimmyJames's Profile: http://forums.techarena.in/members/255792.htm View this thread: http://forums.techarena.in/small-business-server/1357051.htm http://forums.techarena.in You c...

Microsoft Outlook 03-11-10
I have just got a message saying not installed for the current user and it will not load. But I've been using it for years. George George;131443 Wrote: > I have just got a message saying not installed for the current user and > it > will not load. But I've been using it for years. > > GeorgeGeorge, welcome to the forum. When you post (here or in any forum), you will get faster and less frustrating replies if you do a few standard things: - try to define the problem in the thread title. Since this is an Outlook forum, simply stating "Mic...

Problem with macro assigned to custom toolbar button
Problem with macro assigned to custom toolbar button I am having a probelm with macros assigned to a custom button on custom button. I am using excel 2000. 1. I have created a macro called "Jump" in a workbook named "Test1" 2. I then created a custom toolbar and added a custom toolbar button t that toolbar. 3. Through the customize dialog box I assign the Macro "Jump" to th custom button. Everything works fine UNTIL. If I do a SAVE AS for the workbook "Test1" and call it something els like "Test2" and then close "Test2" an...

User unknown
I made a new user on the exchange server, but if i want to make the account in outlook, Outlook says: "the name could not be matched to a name in the adresslist" I don't know what to do anymore. All the rights are the same as my collegea. can somebody help me? Go back to AD where you created the account, preferably on the Exchange Server. Is there an email address listed? If there isn't then right click on the Recipient Update Service in ESM and click update now. Advise what happens then... >-----Original Message----- >I made a new user on the exchange server, b...

Format for credit card numbers
I've tried a custom format for entering credit card numbers (four groups of four digits, separated by dashes: xxxx-xxxx-xxxx-xxxx. I have tried to use ####-####-####-#### and 0000-0000-0000-0000 but each of these causes the last digit to change to zero. So if I enter 5415779800902512 I get 5415-7798-0090-2510. Anybody already solved this problem? -- Schmacker ------------------------------------------------------------------------ Schmacker's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=28041 View this thread: http://www.excelforum.com/showthread.php?th...

limit the number of outgoing messages
Hi, who can I limit the number of concurrent outgoing messages? for example: if I have a queue of 1000 messages, how can I force exchange to send only 50 messages at a time? ...

Macros not performing correctly
I recorded a macro to place a vlookup formula in four cells that are side by side. When I run the macro it only performs the first task, it does not go on to do the other three. I used the tab key to go to the next cell while recording the macro, I don't know if that's why it's not working or if it's something else. The script looks like this: Sub EERates() ' ' EERates Macro ' Macro recorded 3/4/2005 by BCBSOK ' ' ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-2],Sheet1!R3C1:R102C2,2,0)" Range("F15").Select ActiveCell.FormulaR1C...

Root Business Unit Disabled 10-07-05
I inadvertently disabled the root business unit. Now I cannot log in as the administrator was member of the same unit. How do I re-enable this? go into the database and find the table called businessunitbase - then look at the field called isdisabled. You will probably find this is set to 1. try set it to 0 then see if you can logon. Make sure you backup the database before doing this "ril" <ril@discussions.microsoft.com> wrote in message news:CCC43C2F-F109-49BE-A4AC-287AFE0F4D93@microsoft.com... >I inadvertently disabled the root business unit. Now I cannot log in a...

Can't re-enter a previously deleted User ID
We changed the spelling of a User ID (applewicks to appelwicks) and then deleted it (since he couldn't remember his password and the button for password was greyed out so we couldn't change it.) And now we can't re-enter the same user ID even though it doesn't appear in the window any longer. Here is the error we get: ODBC SQL server driver: The log in appelwicks already exists. Thanks! I believe you have to delete the old ID through Enterprise Manager as well. "cliffs" wrote: > We changed the spelling of a User ID (applewicks to appelwicks) and then ...

Numbering in Book Files
I am creating a tech manual where each chapter is its own file. I have created an outline numbered list and saved the list in a template that I then use to create the new chapters. However, I am finding that when I go into a new chapter that the outline numbering does not pick up on the chapter (Heading 1) number. For example: Chapter 1: Heading 1 style 1.1: Heading 2 style 1.1.1: Heading 3 style, etc. But when I start my next chapter, when I update the chapter number to start at 2, I get this: Chapter 2: Heading 1 style 1.1: Heading 2 style 1.1.1: Heading 3 style, etc. ...

Default User object security
Windows 2003 SP2 I am trying to fix a problem where delegation of control is not working properly to usr OUs. I ca't seem to keep permissions on user account objects that allow user accounts to be moved between OUs. I think it may be related to protected account membership on the user objects themselves. ALso the "inherit permissions from parent" is unchecked on user objects. Certain user new user objects work fine and are inheriting. What are the default security to use on user objects so that i can remvoe membership from protected groups and how should I a...

Excel Jululian 04-24-10
Good morning please help i have a lists of names and i want to separate each in a colum like You Light Up My Life197711 You Light Up My Life (in one colum "A") 1977 (in one colum "B") 1 (in one colum "C") 1 (in one colum "E") regards First many thanks on your reply the text are different in length regards "מיכאל (מיקי) אבידן" wrote: > 'Data' > 'Text to Columns' > 'Next' > mark/un-mark the vertical separators as > shown in the picture > 'Finish' >...

Formula to count the number of different values in a range
I'm looking for a formula that will give me the number of different values in a range. Example: Column A may have five cells that are "4", five cells that are "7", five cells that are "9". Of the fifteen cells that contain data, there are only 3 different values. I'd like to use a formula that will count the number of different values in column A, in this case the result is "3". Thanks, Paul Try... =SUMPRODUCT((A1:A15<>"")/COUNTIF(A1:A15,A1:A15&"")) OR =SUM(IF(A1:A15<>"",1/COUNTIF(A1:A...

Removing text from cells leaving numbers (help with function)
I need a function that will remove all text from a cell and just leav numbers. Formatting cells to number does not work. For example if I have: (Sired] Tennessee 37013 (herein I just want 37013 left. Anybody know a function to resolve this -- Message posted from http://www.ExcelForum.com The following will strip the text from the active cell and place the number in the adjcent cell one column to the left. If there are subsequent numbers in the original string you will get erroneous results. Put the cursor on the cell to be processed and run the macro. ********************************...

how to know how many e-mail each user receives per day?
We are running Exchange 2003 on Windows 2003. I would like to know how to get an average of how many e-mails each users send and receives per day. ...

calendar for multiple time zone users: all day events
Users in different time zones post absences and meetings to a shared company calendar. When an all day event is scheduled in one time zone, it shows as spanning two days for other time zone users. How do we make this work properly? For the others it does intrude on a second day. BossLady wrote: > Users in different time zones post absences and meetings to a shared company > calendar. When an all day event is scheduled in one time zone, it shows as > spanning two days for other time zone users. How do we make this work > properly? Until you upgrade to Outlook...

Hyperlinks #10
Is there a way in excel to turn off the hyperlink feature for an entire column of data? I have a column of email addresses, and I don't want any of them to be an active hyperlink. I can remove the hyperlink individually, but have not been able to find a way to do it for the entire column of data. Thanks! Candace A small macro will remove any existing hyperlinks. Sub Delete_HLinks() For Each cel In Selection cel.Hyperlinks.Delete Next cel End Sub To prevent future hyperlinks for the entire sheet go to Tools>Autocorrect Options>AutoFormat as you type. Un-select &q...

Locating a number for a MS office
I have lost the boklet that accompanies my CD. However, I am unable to locate a number to contact someone. Any thoughts. I think if you ever learned how to contact them directly, they'd put a hit out on you. (Seriously, visit the MS website and drill down through the tech support area until you find the numbers you need. Good luck.) -- The problem with resting on your laurels is that eventually you are sitting on dead branches. JoAnn "ziggy" <ziggyzaggy@hotmail.com> wrote in message news:0a4c01c36747$0fb00a40$a001280a@phx.gbl... > I have lost the boklet that...

User not visible in Global Address List
I have one user who from some reason does not show under the Global Address List. They do, however, show under all users and can succesfully send\receive email. All Exchange functionality works as designed. When I go into Exchange Administration, and view the results of the query for the Global Address List, the user shows. This issue is effecting all Exchange 2003 \Outlook 2003 Users. The user is not visible to anyone under Global Address List. I have also check on the properties of the user, and the box to "Hide from Address List" is not checked. Any assistance would ...