Fill rows by user input message box

[tf37]

Found this thread from 2010, but wish to expand on its function
My commented lines show what I’d like to do with it, but in short
' if user entered with no 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?

As always, thanks gang for your input and suggestions


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 no 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

 

[mikerickson]

For i = 0 to 1
    For j = 0 to 99
        Do
            val1 = InputBox("Enter a number")
            If val1 = "999" Then Exit Sub
            If val1 = "" Then MsgBox "oops"
        Loop Until Val1 <> ""

        c.Offset(i,j).Value = val1
    Next j
Next i

I don't understand why you are insisting on 999 to exit when the Cancel button is both present and intuitive.

 

[tf37]

For i = 0 to 1
    For j = 0 to 99
        Do
            val1 = InputBox("Enter a number")
            If val1 = "999" Then Exit Sub
            If val1 = "" Then MsgBox "oops"
        Loop Until Val1 <> ""

        c.Offset(i,j).Value = val1
    Next j
Next i

I don't understand why you are insisting on 999 to exit when the Cancel button is both present and intuitive.

The 999 was for testing for myself to exit the function
As the code was original, pressing ok or cancel did nothing much and still left blank rows
I've run you sample above, and get an error at c.offset(I,j).value=val1
thank you for looking at it for me too - Happy Thanksgiving too
plus pressing ok or cancel didn't exit, so I did the 999 to end the function

 

[mikerickson]

The problem with c.Offset is that c is not a single cell. Try

C.Cells(1,1).Offset(i, j).Value = val1

 

[tf37]

super - that fix that - thank you

I do have another question if I may

The code:
Sub fill_JurorFormNumbers()
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
Set c = ActiveCell

For i = 0 To 10
For j = 0 To 10

Do
val1 = InputBox("Please enter number", "Enter Number, Enter 999 To Finish")
If val1 = "999" Then Exit Sub
If val1 = "" Then MsgBox "OOPS"
Loop Until val1 <> ""
c.Cells(1, 1).Offset(j, i).Value = val1
Next j
Next i
End Sub

How would you get it to switch to a different column?
It starts in column A and runs the set of 10, then moves to column B and continues the input, but to help keep in on one screen for my clerks, can I get it to shift from column A once done and move to column C?
Column B will be reserved for random number generation, along with column d
The goal of my little project here (little - always) is help my clerks with a requested excel sheet that allows us to enter the number from a juror form number assigned, then we'd enter the random number in the next column
once all said in done, the sort of the random number columns will be the order the juror is called to the juror box.
I've tried different things like c.cells(1, 3) and reversed since "help" says (rows,columns) but it didn't fly.
Using Excel 2007, Win 7 Pro
Still using the 999 to exit has me a little baffled, since if you click cancel on the input box it does nothing but display the "oops" only way I can end the sub is with the 999 to exit sub, but it works just fine for me too.
Thanks again for the great support

 

[mikerickson]

Dim i as Long, j as Long, val1 as Variant
Dim c As Range

Set c = Selection.Cells(1,1)

For i = 0 to 1
    For j = 0 to 99 step 2
        Do
            val1 = InputBox("Enter a number")
            If val1 = "999" Then Exit Sub
            If val1 = "" Then MsgBox "oops"
        Loop Until Val1 <> ""

        c.Offset(i,j).Value = val1
        c.Offset(i, j+1).Value = rnd()
    Next j
Next i