Iswearimnotdumb
New Member
- Joined
- Mar 20, 2022
- Messages
- 7
- Office Version
- 365
- Platform
- Windows
I'm working on a spreadsheet for my dad for his golfing group.. Thanks to this forum, we already have a code for automatically shifting the data over a column when new data is entered and we have the formulas in the spreadsheet configured so they do not shift with the data shifting.
What he's wanting is when data is entered into column B, it automatically clears column B after it moves into column E.
Also (not sure if this one is possible), that column D, which is the average of columns E through N, does not decrease more than 2 (value in A1).
Any help or tips would be greatly appreciated!
The code currently in the spreadsheet:
Private Sub Worksheet_Change(ByVal Target As Range) 'Excel VBA with more cells in the range.
'
If Not Intersect(Target, Range("B2:B55")) Is Nothing Then
Application.EnableEvents = False
'
Range("E" & Target.Row & ":M" & Target.Row).Cut Range("E" & Target.Row & ":M" & Target.Row).Cells(1).Offset(0, 1)
Range("E" & Target.Row) = Range("B" & Target.Row)
'
Application.EnableEvents = True
End If
End Sub
What he's wanting is when data is entered into column B, it automatically clears column B after it moves into column E.
Also (not sure if this one is possible), that column D, which is the average of columns E through N, does not decrease more than 2 (value in A1).
Any help or tips would be greatly appreciated!
WCC Dogfight_ses_working.xlsm | |||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | |||
1 | 2 | ||||||||||||||||
2 | Players | Points | Net +/- | Quota | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | Total | ||
3 | Allen, Frankie | 5 | 17 | 5 | 5 | 18 | 19 | 19 | 22 | 23 | 21 | 19 | 151 | ||||
4 | Allman, Willie | 28 | 27 | 30 | 19 | 31 | 27 | 27 | 30 | 32 | 28 | 28 | 279 | ||||
5 | Arbaugh, Donnie | 23 | 27 | 19 | 23 | 24 | 17 | 22 | 25 | 23 | 24 | 22 | 226 | ||||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
D3:D5 | D3 | =AVERAGE(INDEX($E:$E,ROW()):INDEX($N:$N,ROW())) |
O3:O5 | O3 | =SUM(INDEX($E:$E,ROW()):INDEX($N:$N,ROW())) |
The code currently in the spreadsheet:
Private Sub Worksheet_Change(ByVal Target As Range) 'Excel VBA with more cells in the range.
'
If Not Intersect(Target, Range("B2:B55")) Is Nothing Then
Application.EnableEvents = False
'
Range("E" & Target.Row & ":M" & Target.Row).Cut Range("E" & Target.Row & ":M" & Target.Row).Cells(1).Offset(0, 1)
Range("E" & Target.Row) = Range("B" & Target.Row)
'
Application.EnableEvents = True
End If
End Sub