macros with Pulldown menu

[Sharid]

I have several staff you need to complete training within deadlines. To keep myself on track with their progress I devised a spreadsheet to with a drop down menu option to select an answer of YES or NO depending on if they had completed training. After a while this became a pain as I had to select the NO option for all staff and then later change the NO into a YES once training had been completed.

I tried to set a macro so when clicked it would input all the NO options and I could later use the pull down to change them to a YES. My problem is my macro does not work.

Selection.AutoFill Destination:=Range("E9:E33"), Type:=xlFillDefault
Range("E9:E33").Select
Range("E9").Select
End Sub

The cells are E9 to E33 in column E

Once the macro is activated I want E9 to E33 to fill with the word NO. I can later use the pull down to change cells to a YES option once training is complete. Also what I need is that once the cells E9 to E33 are full the next time the macro is activated cells F9 to F33 in column F will read as NO and so on everytime the macro is activated.

Is this possible?

cheers

 

[VoG]

To do the first part is straightforward

Range("E9:E33").Value = "NO"

will the column to be filled with NOs always be the first blank column (or at least Row 9 of the column blank)?

 

[Sharid]

row 9 of the blank column, the blank column will always be from row 9 to row 33

 

[VoG]

Try this

Sub InsertNO()
Dim NC As Integer
NC = Cells(9, Columns.Count).End(xlToLeft).Column + 1
Range(Cells(9, NC), Cells(33, NC)).Value = "NO"
End Sub

 

[Sharid]

Thanks for the macro, I tested it in my sheet but it does not work. so I tested it in an new blank SHEET and it worked. It input the word NO from cell 9 to 33. However it did not start in column E from E9 to E33. It started from B9 to B33.

Every time i clicked the button it filled a new colum from cell 9 to cell 33. This is great, however it does not work on my sheet.

Is it because I have a pulldown option that start in E9 to E33 with the options YES and No?

This is also the case for all columns on my sheet from cells 9 to 33 all coloums and cells within have a pulldown option of YES or NO.

I will not need the "NO" option if you macro works.

Once again thanks

 

[Sharid]

Don't ask why, but its has decided to work in my sheet now and it works a dream.

Thank You

I owe you one. If you ever want to learn how not to write good macros, then i'm you man

cheers