Golf Spreadsheet - Autoclearing column after input and inserting "most minus"

Iswearimnotdumb

New Member
Joined
Mar 20, 2022
Messages
7
Office Version
  1. 365
Platform
  1. 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!

WCC Dogfight_ses_working.xlsm
ABCDEFGHIJKLMNO
12
2PlayersPointsNet +/-Quota12345678910Total
3Allen, Frankie5175518191922232119151
4Allman, Willie2827301931272730322828279
5Arbaugh, Donnie2327192324172225232422226
Sheet1
Cell Formulas
RangeFormula
D3:D5D3=AVERAGE(INDEX($E:$E,ROW()):INDEX($N:$N,ROW()))
O3:O5O3=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
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

johnnyL

Well-known Member
Joined
Nov 7, 2011
Messages
4,091
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
To clear the Column B value after it is moved, place the following before the 'Application.EnableEvents = True' line:
Range("B" & Target.Row) = ""
 
Upvote 0
Solution

Forum statistics

Threads
1,186,382
Messages
5,957,537
Members
438,311
Latest member
OutLine86

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top