How do I append user input to the first empty row in a sheet?

Question

I am writing VB code for a Excel form that prompts the user to answer a series of questions and then stores the responses in rows on a sheet. At present the code stores the first response in A2 then second response in B2 and so forth. The sub ends when a thank you prompt appears on the screen.

What I would like to do is when all questions are answered that the cursor will move to the first cell of the next row (A3) to store the answers to the same questions for another person. It must keep on moving to the next row.

These are the main pieces of code

Sub dform ()
    Dim mName As String
    mName = InputBox("What is your maiden named", "Maiden Name")
    Range("A2").Select
    ActiveCell.FormulaR1C1 = mName
    x = MsgBox("Are you still married?", 4)
    If x = 6 Then Range("G2").Value = "Yes"
    If x = 7 Then Range("G2").Value = "No"
    Exit Sub
End Sub

Answer 1

First of all, you may want to edit your question, as these are asking very different things:

What is your maiden named
What is your maiden name?

I've made a few modifications to your code. The comments should help you understand what is happening. Using this approach allows you to ask your questions without having to select or display the sheet that has all the answers.

I've replaced your hard-coded row with a variable that is set to the first empty row in column A of the ws object. You can set ws to whatever your sheet is called. Now you can run this as many times as you want and it will always append the new answers to a new row.

' use this statement at the top of all modules to require variable declaration
Option Explicit

Sub dform()
    ' declare your variables
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim firstEmptyRow As Long
    Dim mName As String
    Dim x As Long

    ' you need the "set" keyword for object variables
    Set wb = ThisWorkbook
    Set ws = wb.Sheets("Sheet1")
    ' the best way to get the last row is to go up from the bottom of the sheet
    ' add 1 to get the first empty row
    firstEmptyRow = ws.Range("A" & ws.Rows.Count).End(xlUp).Row + 1

    mName = InputBox("What is your maiden named", "Maiden Name")

    ' always avoid selecting or activating in VBA code
    ws.Range("A" & firstEmptyRow).Value = mName
    x = MsgBox("Are you still married?", 4)
    If x = 6 Then ws.Range("G" & firstEmptyRow).Value = "Yes"
    If x = 7 Then ws.Range("G" & firstEmptyRow).Value = "No"
    Exit Sub
End Sub

Answer 2

You might try using the Cells or Offset properties in a loop:

http://msdn.microsoft.com/en-us/library/office/aa139976(v=office.10).aspx

Answer 3

Two possible solutions are:

• You store the number of the last used row in a hidden sheet
• You "read" the info in the sheet and store the data in the first empty row

I think the first approach is the easiest way to go, and it is persistent (the row number is stored when you save the book).

So, let's assume that you have a sheet called utilitySheet and you store the last row used in cell B2. The value must be an integer, of course.

So your can be something like this:

sub dform()
    dim mName as String
    dim nRow as Integer

    nRow = ThisWorkbook.Sheets("utilitySheet").Cells(2,2).Value
    ' ...
    If x = 6 then ThisWorkbook.Sheets("results").Cells(nRow + 1, 7).Value = "Yes"
    If x = 7 then ThisWorkbook.Sheets("results").Cells(nRow + 1, 7).Value = "No"
    ' ...
    ' Update the row number in the utility sheet
    ThisWorkbook.Sheets("utilitySheet").Cells(2,2).Value = nRow + 1
end sub