Use input box to fill cells
Attend Excelapalooza
Thanks Thanks:  0
Results 1 to 7 of 7

Thread: Use input box to fill cells

  1. #1
    Board Regular
    Join Date
    Aug 2003
    Posts
    148
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Use input box to fill cells

     
    I want to fill a range of cells 5 rows by 2 columns with numbers, I want to do this from the active cell, how would you go about making a macro that will bring up and input box and put the number you enter in the active cell and then the input box would come back up and the next number would be put in the cell below it, do this for 5 rows then start in the next column and put in the next 5 numbers you enter, so lets say I started in A4 the first number would go in A4 then A5,A6,A7,A8,B4,B5,B6,B7,B8.
    Thanks

  2. #2
    Board Regular
    Join Date
    Mar 2009
    Location
    Scotland, UK
    Posts
    137
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Use input box to fill cells

    Enter this into a new module, and assign it to a button or just run it:

    Code:
    Sub fill()
    Dim c As Range
     
    For Each c In sheet1.Range("A4,A5,A6,A7,A8,B4,B5,B6,B7,B8")
                 c.Value = InputBox("Please enter number", "Enter Number")
    
    Next c
    
    End Sub

  3. #3
    Board Regular
    Join Date
    Mar 2009
    Location
    Scotland, UK
    Posts
    137
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Use input box to fill cells

    sorry I misread your question, that is a set range. To do this depending on the activecell, use this cheap and nasty solution:

    Code:
    Sub fill()
    Dim c As Range
    Set c = ActiveCell
    c.Value = InputBox("Please enter number", "Enter Number")
    c.Offset(1, 0).Value = InputBox("Please enter number", "Enter Number")
    c.Offset(2, 0).Value = InputBox("Please enter number", "Enter Number")
    c.Offset(3, 0).Value = InputBox("Please enter number", "Enter Number")
    c.Offset(4, 0).Value = InputBox("Please enter number", "Enter Number")
    c.Offset(0, 1).Value = InputBox("Please enter number", "Enter Number")
    c.Offset(1, 1).Value = InputBox("Please enter number", "Enter Number")
    c.Offset(2, 1).Value = InputBox("Please enter number", "Enter Number")
    c.Offset(3, 1).Value = InputBox("Please enter number", "Enter Number")
    c.Offset(4, 1).Value = InputBox("Please enter number", "Enter Number")
    End Sub
    Someone might suggest a more efficient way, but for 10 cells this will certainly do

  4. #4
    Board Regular
    Join Date
    Aug 2003
    Posts
    148
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Use input box to fill cells

    Thanks that works, would there be anyway to exit out of the macro before you get to the end?

  5. #5
    Board Regular
    Join Date
    Mar 2009
    Location
    Scotland, UK
    Posts
    137
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Use input box to fill cells

    Hi,

    I had a bit time thismorning so tidied up the code and enabled the user to cancel.

    try this:

    Code:
    Sub fill()
     
    Dim c As Range
    Set c = ActiveCell
     
    For i = 0 To 1
    For j = 0 To 4
     
    val1 = InputBox("Please enter number", "Enter Number")
    
    If val1 = "" Then
    Exit Sub
    Else
    c.Offset(j, i).Value = val1
    End If
     
    Next j
    Next i
     
    End Sub

  6. #6
    Board Regular
    Join Date
    Aug 2003
    Posts
    148
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Smile Re: Use input box to fill cells

    Thanks that works great

  7. #7
    Board Regular tf37's Avatar
    Join Date
    Apr 2004
    Location
    American Samoa
    Posts
    149
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Use input box to fill cells

      
    I found this with some searching, but does what I like...just wish to expand on it
    My code below shows with commented sections on how I'd like to expand on it some

    Sub fill()
    Dim c As Range
    Set c = ActiveCell

    c = Range("A2:A91") 'inserted to select my range to use- works ok
    Range("A2").Select ' inserted again to select my range to use - works ok

    For i = 0 To 1
    For j = 0 To 99

    val1 = InputBox("Please enter number", "Enter Number, Enter 999 To Finish")

    If val1 = "999" Then
    Exit Sub
    Else
    c.Offset(j, i).Value = val1
    If val1 = "" Then
    MsgBox "You blew it if your are not done"
    ' if user entered with a value, it should resume at that place it left off in the row population
    ' as it is, it is skipping a row and leaving a blank row with no value
    ' need correction to back up and resume where it left off to populate rows in sequence - no blanks until 999 is entered
    ' i'd still like a msgbox to inform the data input person that they accidently pressed enter without proper exit with 999
    ' if it isn't a major code project that is, otherwise, just continue but in row sequence with no blank rows until 999 is entered to finish
    ' plus the input box at the beginning displays "ok" and "cancel", suppose that is by default, can those be removed allowing only a 999 to exit?
    End If
    End If

    Next j
    Next i

    End Sub
    As usual, lost again, and looking for help from the guru's. You folk's are da most.
    Thanks,
    Terry

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com