Use input box to fill cells

lex_m

Board Regular
Joined
Aug 5, 2003
Messages
155
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
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
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
 
Upvote 0
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
 
Upvote 0
Thanks that works, would there be anyway to exit out of the macro before you get to the end?
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,114,002
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top