Change a column data without new row?

  • Thread starter Thread starter PG Oriel
  • Start date Start date
P

PG Oriel

Hi I currently have this which puts info onto a detention sheet for me when
they get their 1st warning (a 1 in this case). This would denote a 5 minute
detention which I would write in column D of the detentions sheet.

What I would like is that when I move to warning two it automatically
changes the 5 minute to a 15 minute one, and then 30 minute on warning 3. Is
this possible?

Case Empty
Target.Value = "!"
Case "!"
Target.Value = "1"
Sheets("Detentions").Activate
Set rng = Sheets("Detentions").Cells(Rows.Count,
"B").End(xlUp).Offset(1, 0)
rng.Value = Cells(Target.Row, "B").Value 'Fills Name in
Column B
rng.Offset(0, 1).Value = Cells(1, Target.Column).Value
'Fills Date in Column C
rng.Offset(0, -1).Value = Cells(Target.Row, 1).Value 'Fills
Teacher in Column A
Sheets("Behaviour").Activate
Case "1"
Target.Value = "2"
Case "2"
Target.Value = "3"
 
It puts it in the row created in the code after Pupil A's "1" gets entered.
It is not always possible to assume that it is still the last row in the
detentions sheet either as another pupil might have received a "1" before
Pupil A gets a "2"
 
Back
Top