Loop question
 

I am trying to create a Do Until loop that will display a form that will
display until a checkbox is checked. The answer to the question on the form
needs to be written to the spreadsheet in the cell below the previous answer.
I'm fairly new to this, but I'm guessing I need to use Offset. I've never
done that before. Anyone have any suggestions and/or hints? Thank you!

Loop question
 

Once the checkbox is checked and you presumably have a valid answer, this
kind of logic in your code should work. Presumes that the answer is held in
variable myAnswer (could come from text entry on the form itself), and the
column to store the answers in is column B.

ActiveSheet.Range("B" & Rows.Count).End(xlUp).Offset(1,0) = myAnswer

The ActiveSheet.Range("B" & Rows.Count).End(xlUp) portion locates the last
used cell in column B by looking from the bottom of the sheet toward to the
top. As you'd guessed, the .Offset(1,0) gives the location just below the
last used cell in the column, or the first blank available in that column, if
you look at it that way.

If you're using Excel 2007, use Rows.CountLarge instead of Rows.Count

"aelewis" wrote:

I am trying to create a Do Until loop that will display a form that will
display until a checkbox is checked. The answer to the question on the form
needs to be written to the spreadsheet in the cell below the previous answer.
I'm fairly new to this, but I'm guessing I need to use Offset. I've never
done that before. Anyone have any suggestions and/or hints? Thank you!

Loop question
 

Thank you! I will try that.

"JLatham" wrote:

Once the checkbox is checked and you presumably have a valid answer, this
kind of logic in your code should work. Presumes that the answer is held in
variable myAnswer (could come from text entry on the form itself), and the
column to store the answers in is column B.

ActiveSheet.Range("B" & Rows.Count).End(xlUp).Offset(1,0) = myAnswer

The ActiveSheet.Range("B" & Rows.Count).End(xlUp) portion locates the last
used cell in column B by looking from the bottom of the sheet toward to the
top. As you'd guessed, the .Offset(1,0) gives the location just below the
last used cell in the column, or the first blank available in that column, if
you look at it that way.

If you're using Excel 2007, use Rows.CountLarge instead of Rows.Count

"aelewis" wrote:

I am trying to create a Do Until loop that will display a form that will
display until a checkbox is checked. The answer to the question on the form
needs to be written to the spreadsheet in the cell below the previous answer.
I'm fairly new to this, but I'm guessing I need to use Offset. I've never
done that before. Anyone have any suggestions and/or hints? Thank you!