Forum Discussion
Automatically replace old data with new data
biza_j With a setup like this, where the new scores are input in row 2...
Multi-column setup
...you could try the following modified code:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
'Stop the macro if more than one cell was changed
If Target.Cells.Count > 1 Then Exit Sub
'Verify an input cell in row 2 was changed and is not blank
Dim rg As Range, lastCol As Long
lastCol = Me.Cells(1, Me.Columns.Count).End(xlToLeft).Column
Set rg = Me.Range("A2").Resize(, lastCol)
If Not Intersect(Target, rg) Is Nothing And Target.Value <> "" Then
' validate the new score
If Not Application.WorksheetFunction.IsNumber(Target.Value) Then
' display validation error
MsgBox "Please enter numeric values only", vbCritical, "Invalid Score"
Else
' update the last 10 scores
Set rg = Target.Resize(10)
rg.Offset(1).Value = rg.Value
' clear the input cell
Target.ClearContents
End If
Target.Select
End If
End Sub
Thank you,
The first column is still replacing data after the tenth entry, but this is not extending to columns 2 on wards.
Made the adjustment to enter data into row 2 instead of row 1 unless i am messing something else up.
biza_j Interesting... I don't have that problem with the code and setup I shared. There were a number of little changes I made to the code from my original post. If you didn't copy and paste the updated code in its entirety, but rather made individual edits to your own existing code, there may have been one or two changes that were missed.
Please see the updated workbook below, if needed...
No I have used your file and started inputting data.
the first column works for 10+ entries of data, the second column works for 8 data entries then stops clearing row 2 and all other columns after don't add the data to the the cells below at all.
biza_j What happened to all of the student names in row 1 (Student 1 to 25 in my example file)? Did they disappear on their own while you were inputting scores (they shouldn't have), or did you delete them manually?
These lines of code were meant to dynamically identify the input range, based on the student names entered in row 1:
'Verify an input cell in row 2 was changed and is not blank Dim rg As Range, lastCol As Long lastCol = Me.Cells(1, Me.Columns.Count).End(xlToLeft).Column Set rg = Me.Range("A2").Resize(, lastCol) If Not Intersect(Target, rg) Is Nothing And Target.Value <> "" ThenSo, if there are no names entered in row 1, lastCol will be column 1 and cell A2 will be the only cell recognized as part of the input range.
If you want to simply designate a static range as the input range (e.g. A2:Y2), change those lines of code to the following:
'Verify an input cell was changed and is not blank Dim rg As Range: Set rg = Me.Range("A2:Y2") If Not Intersect(Target, rg) Is Nothing And Target.Value <> "" ThenThat should do it. If not, let me know...