Clear selected cell and change the content of those around it.

[Nyge2k3]

Can anyone help me with this? I'm sure there's a simple macro for it, but I'm not very good with them unless I'm recording. Basically, I'm doing staff rotas and looking for an easy way to show someone on leave. I could need to change many at the same time, so I'm looking for a bit of a time saver. So.....

Currently (Where selected Cell = A1):

A1 = 06:30
A2 = 13:30
A3 = 07:00
A4 = 07:00
A5 = BLANK
A6 = "TEXT"
A7 = "TEXT"

I want to change it to:

A1 = BLANK
A2 = BLANK
A3 = "00:00"
A4 = BLANK
A5 = "Leave"
A6 = BLANK
A7 = BLANK

Many thanks for any help

 

[Micron]

Those same 7 cells would get those same values every time the code runs? You could loop over an array of those values in the order you need
e.g. For i = 1 to 7
and in the same loop, set "A" & i to the value retrieved by array(i)

 

[Nyge2k3]

Hi, thanks for the help.

I found this as the solution.

ActiveCell.Select
Selection.ClearContents
ActiveCell.Offset(1).Select
Selection.ClearContents
ActiveCell.Offset(2).Select
Selection.ClearContents
ActiveCell.Offset(1).Select
ActiveCell.FormulaR1C1 = "PL"
ActiveCell.Offset(1).Select
Selection.ClearContents
ActiveCell.Offset(1).Select
Selection.ClearContents

End Sub