VBA (Excel) Need to cycle through strings in active cell

[scaiferw]

Sorry in advance if this is the wrong place for this, it's the closest I could find.

I need to create a macro for an Excel 2010 spreadsheet so that the active cell will cycle through three choices: "Y", "N" or "" - the last being simply an empty string that will leave the cell empty.

I've done a lot in PHP and a couple of other scripting languages, but nothing in any current form of VB, so I'm quite at a loss. Google has not helped me on this one.

Would be very grateful for any assistance to get me going.

[aVar++]

I can't completetly see what you are trying to do, but to referance a cell, you can use:

Application.Range("CELL eg A4").value = "Y"

Could you explain in a bit more detail what you are trying to do?

[scaiferw]

I'm listing test steps in an Excel spreadsheet. With the active cell in the "Results" column, I'd like to be able to use a macro to cycle through two values, or better yet three, "Y", "N" or "".

Logic along the lines of:

if (active cell contains "") then enter "Y"
  else if (active cell contains "Y") then enter "N"
  else if (active cell contains "N") then enter ""
endif

I'd attach this macro to a function key for ease of use.

Would appreciate any assistance. I'm sure this is ridiculously simple for an expert in Excel.

[scaiferw]

OK then.

Select Case UCase(ActiveCell.Value)

  Case ""
    ActiveCell.Value = "Y"

  Case "Y"
    ActiveCell.Value = "N"

  Case "N"
    ActiveCell.Value = ""


  Case Else
    MsgBox "Active Cell must be empty or contain 'Y' or 'N'"

End Select