Loop question

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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!
 
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
 
Thank you! I will try that.

JLatham said:
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
Click to expand...
 
Back
Top