Use input box to fill cells

[lex_m]

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

 

[dodger7]

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

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

 

[dodger7]

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

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

 

[lex_m]

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

 

[dodger7]

Hi,

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

try this:

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

 

[lex_m]

Thanks that works great

 

[tf37]

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