Use VBA to automate entry of schedule, scores

[DocAElstein]

Hello scotty68
Welcome to excelfox

I do not understand American Football, but I will try to answer your question.

Because I do not understand how these things are organised, I may not get the solution exactly as you want.

But this may help you to get the final solution exactly as you want.

One question I have for you please: The formula solution seems perfectly adequate. So why do you need a VBA solution??

---

Anyway, Here we go….

... use VBA to automatically enter scores on the right side from the left side.

It is not 100% clear to me exactly what you are asking for.
Possibly you are asking for VBA to do exactly the same as what you are doing with the formulas?

I will assume that you just want VBA to do the same as the formulas.

As you have given us very sparse information, I will just help get you started and leave you to modify to get exactly what you want. Or maybe it will be exactly what you want. I am not sure.

I assume you don’t know VBA too well.
The way to get things to work automatically is to tap into coding routines which are already in Excel but have not got any code lines in yet. The makers of Excel have included, for example, routines which kick in every time a worksheet is changed. But they don’t do anything as they do not have any code lines in them.
To get one of the routines we want we do this:
Right Click on Tab Select View Code : https://imgur.com/xkQqnih
Right Click on Tab Select View Code.JPG

After that you should see the VB Editor, and the big window is for coding related to the worksheet, in this case worksheet “2019”
VB Editor showing worksheet code module 2019 : https://imgur.com/yKVOEI2
VB Editor showing worksheet code module 2019.JPG

Now we need to fiddle around with the two drop downs until we get one of the existing empty routines shown, we need the Change one:
Worksheet event coding : https://imgur.com/t8BqQNA
Worksheet event coding.JPG

Change event routine : https://imgur.com/IXIa7ly
Change event routine.JPG

That routine is always there ( hidden ) and kicks in every time any cell value is changed. But it does nothing as there is no coding in it
I will put some coding in it now. In VBA there are loads of ways to do most things. I will do the first way that comes into my head to do the VBA equivalent to the first couple of formulas. In other words, this coding will replace these two formals:
http://www.excelfox.com/forum/showth...ll=1#post11132
It should be fairly obvious how you extend the coding for all weeks

Brief code description
The Target thing is the range object of the cell or cell that was changed. That is somehow passed to the routine automatically by Excel whenever a cell or cell is changed.

It is typical common practice to limit the running of such coding to just when the cells of interest to us are changed. In the example I am doing for you, it will be the ranges B3:Q4 and B10:Q11

In VBA there is a function, ( https://docs.microsoft.com/de-de/off...tion.intersect ) which gives you either a range object , or Nothing , based on the area that ranges cross, ( intercept). So if we look at what the function givrs us for where B3:Q4 and Target cross, or where B10:Q11 and Target cross, then it should Not give us Nothing if we changed anything in B3:Q4 or B10:Q11
__ If we get Not Nothing then we need to do the summing…

---

There you go. Hope that helps get you started.
There is probably a more clever, much shorter coding to do this for all weeks based on a bit of clever maths. But this way is a bit easier to understand

Alan

Code to put in your worksheet code module

Private Sub Worksheet_Change(ByVal Target As Range)
'week # 1
    If Not Application.Intersect(Target, Range("B3:Q4")) Is Nothing Then  ' time to do something
     Let Application.EnableEvents = False ' This stops this code kicking itself off when it changes a cell value
     Let Range("R3").Value = Me.Evaluate("=sum(B3:Q4)") ' Evalute gives you what you would get if you typed the thing inside the " " into a cell
     Let Application.EnableEvents = True ' We should turn back on event coding
    Else ' case no change in Score area
    End If
'week # 2
    If Not Application.Intersect(Target, Range("B10:Q11")) Is Nothing Then ' time to do something
     Let Application.EnableEvents = False ' This stops this code kicking itself off when it changes a cell value
     Let Range("R10").Value = Me.Evaluate("=sum(B10:Q11)")
     Let Application.EnableEvents = True ' We should turn back on event coding
    Else ' case no change in Score area
    End If
End Sub