Macro to fill in cells from a column sequentially when I click on a button

[Mary_z]

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

This is my layout:



As you can see, I have 2 buttons.
What I would like each button to do is:
1. Each time I click on YES, it will insert a Y in a cell in a specific range.
2. If I click on YES again, it will automatically insert a Y in the next cell under the previous one in the same column.
3. As you can see, if I have to click on NO, it will also continue down the column and insert an N.


If someone can help me out with this I'd be very grateful!

 

[SteveO59L]

something like (for the "Yes" option)

targetrw = Range("m" & Rows.Count).End(xlUp).Row + 1
Range("m" & targetrw) = "Y"

 

[tapedeck]

The code for can be written as easily as:

Sub YesButton()

    ActiveCell.Value = "Y"
    ActiveCell.Offset(1, 0).Select

End Sub

Sub NoButton()

    ActiveCell.Value = "N"
    ActiveCell.Offset(1, 0).Select

End Sub

You could add those codes to a standard module (from the VBA window, insert module, and paste it into the resulting window). From the spreadsheet, in Design Mode, right-click each button, and assign the applicable macro. Get out of Design Mode, click on the first blank cell in your column, and start clicking your buttons.

You could write this more slickly (the code needn't be in a public module, you could write some extra code to find the first blank cell, etc), but this is the nuts-and-bolts of it.

 

[tapedeck]

See: SteveO59L, more slick. You can replace his code with my ActiveCell statements in the YesButton code, and that finds the blank cell, rather than moving your selections around the spreadsheet (generally bad form).

 

[Mary_z]

Thanks SteveO59L and tapedeck
I tried it on a blank excel sheet and it works fine.
The issue I'm having now is that I need it to start at AS11 to AS500 on the sheet I'm currently working on which has a lot of stuff on it.

I tried this:

targetrw = Range(as11, [as500] & Rows.Count).End(x1Up).Row + 1
Range(as11, [as500] & targetrw) = "Y"

I get no error, but nothing is being inserted.

EDIT, I also just tried using the column AS (no range) but still nothing comes up.
Could another macro on this sheet be interfering?

 

[BlokeMan]

cross post;Macro to fill in cells from a column sequentially when I click on a button

 

[Mary_z]

cross post;Macro to fill in cells from a column sequentially when I click on a button

Like I said Blokeman, if you see.. all the solutions these helpful people have given me are completely different. This makes be learn a lot.
I also always thank each and everyone one that helped me out.
I don't know why you are the "cross post" police or anything.

 

[dmt32]

Hi,
see if this slight modification to SteveO59L suggestion helps you:

targetrw = Range("AS" & Rows.Count).End(xlUp).Row + 1
If targetrw < 11 Then targetrw = 11
If targetrw <= 500 Then Range("AS" & targetrw) = "Y"

Dave

 

[Mary_z]

Hi,
see if this slight modification to SteveO59L suggestion helps you:

targetrw = Range("AS" & Rows.Count).End(xlUp).Row + 1
If targetrw < 11 Then targetrw = 11
If targetrw <= 500 Then Range("AS" & targetrw) = "Y"

Dave

That worked PERFECTLY, Dave!!!
Many thanks!

 

[BlokeMan]

Like I said Blokeman, if you see.. all the solutions these helpful people have given me are completely different. This makes be learn a lot.
I also always thank each and everyone one that helped me out.
I don't know why you are the "cross post" police or anything.

You should read this forum RULES #10:http://www.mrexcel.com/forum/board-announcements/99490-forum-rules.html