0

I'm having a lot of trouble trying to figure this out.

This is my layout:

screenshot of the layout

As you can see, I have 2 buttons.

What I would like each button to do is:

  1. Each time I click YES, it will insert a Y in a cell in a specific range.
  2. If I click YES again, it will automatically insert a Y in the next cell under the previous one in the same column.
  3. If I click NO, it will also continue down the column and insert an N.

How to make the buttons' output to always use the next free cell?

1
  • 1
    Welcome to Super User. Can you share the code you have so far, so we can see where to help you?
    – CharlieRB
    Feb 20, 2015 at 12:17

1 Answer 1

3

I'm going to assume the rest of your code is done and what you need is some method to get a row for inserting your values. Here's a snippet that works best if your data isn't very large and you happen to prefer loops:

Dim rNext as Long
rNext = 2   'First used row in column F
Do Until LenB(Range("F" & rNext).Value) = 0
    rNext = rNext + 1
    If rNext = ActiveSheet.Rows.Count Then Exit Do
Loop

And here's a solution that's more robust and quite a bit faster:

Dim rNext As Long
rNext = Range("F2:F" & Cells.Rows.Count).Find(vbNullString).Row

As you might be able to tell from the second example, you could do this directly with the following:

Range("F2:F" & Cells.Rows.Count).Find(vbNullString).Value = "Y"
1
  • +1 Good solution. OP, also see this answer for some additional clarification. Feb 20, 2015 at 14:03

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .