VBA - input box result to next empty cell

[Stephen.Nichols]

Hi <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
I am struggling with my vba code. I need a code that:<o></o>
<o> </o>
1. Prompts a user input via an inputbox<o></o>
2. Place the input box result in the next empty cell in a range<o></o>

Can anyone help?

 

[mvptomlinson]

Hi Stephen, something like this should work:

Sub nichols()
Dim ans as String, lr as Long
ans = InputBox("Enter some value", "Data Entry Form")
If ans = "" Then
    Exit Sub
Else
    lr = Range("A" & Rows.Count).End(xlUp).Row + 1
    Range("A" & lr).Value = ans
End If

 

[Leith Ross]

Hello Stephen.Nichols,

This will put the entry into the next empty cell in column "A" of the active sheet.

  Dim Answer As Variant
  Dim NextCell As Range

    Set NextCell = Cells(Rows.Count, "A").End(xlUp)
    If NextCell.Row > 1 Then NextCell = NextCell.Offset(1, 0)

    Answer = MsgBox("Enter the value.")
    If Answer = "" Then Exit Sub
 
    NextCell = Answer

 

[mvptomlinson]

Leith, wouldn't you need an InputBox, not MsgBox?

 

[Leith Ross]

Thanks for the catch! You are right. I meant InputBox.

Revised Code:

  Dim Answer As Variant
  Dim NextCell As Range

    Set NextCell = Cells(Rows.Count, "A").End(xlUp)
    If NextCell.Row > 1 Then NextCell = NextCell.Offset(1, 0)

    Answer = InputBox("Enter the value.")
    If Answer = "" Then Exit Sub
 
    NextCell = Answer

 

[Stephen.Nichols]

Great thanks guys. Both work well

 

[AM2014]

Hi,

I've tried to manipulate this to work for me, but I have hit a wall. I need the code to prompt me with an input box when it finds a blank cell in column O. I have a couple problems with the above code: 1) I do not want the code to input the same number for each of the blank cells in column O, but instead prompt me each time a blank cell is found. 2) I need to see the adjacent cells in order to know what to input. Right now it is just showing the top rows, not the blank one.

Thanks for any help!