I have a spreadsheet to keep track of NFL scores every season. I have a formula to retrieve the scores on the right side from the scores on the left side. I would like to use VBA to automatically enter scores on the right side from the left side.
Printable View
I have a spreadsheet to keep track of NFL scores every season. I have a formula to retrieve the scores on the right side from the scores on the left side. I would like to use VBA to automatically enter scores on the right side from the left side.
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….
It is not 100% clear to me exactly what you are asking for.Quote:
Originally Posted by scotty68
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
Attachment 2239
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
Attachment 2240
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
Attachment 2241
Change event routine : https://imgur.com/IXIa7ly
Attachment 2242
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
Code: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
I was thinking along the lines of the right side of the sheet, where I have multiple teams and the points scored and against them each week. I have a long Index/Match formula retrieving said points each week. Is there a way, using VBA, to cut that down to just a shorter formula to get the points and also not using the 0's and 1's in row 26 and others? Or maybe also using names on the left side to get said points? There's only two choices of getting the points for each week, and there's points for(PF), and points against(PA). When you reply, would you post in English please?
Hello scotty68
Ah, I missed seeing all that.Quote:
Originally Posted by scotty68
Once again, I think everything will be a lot less obvious to me as I don’t know American Football, or your project as you do. Things like “ NFL scores every season mean nothing to me.
I am not quite sure what you mean byif you mean less technical jargon, then that will be a bit difficult. I have a reputation for explaining things in a lot simpler terms then most people. So I think you will never find a less technical jargoned explanation anywhere then what I give. VBA, and Object Orientated Programming is a bit of a odd concept until you know it. Even then it still has a few peculiar terms and concepts..Quote:
Originally Posted by scotty68
( If you mean about the German Excel screenshots. .. Sorry about that, but I have German Excel. ( I live in Germany ).. Usually the things I show are in the same place in English Excel, and I have mostly marked them with an arrow. In English Excel they will look very similar )
If you just want the finished answer, working, no explanations, then fair enough. Help Forums are usually not the place to go for that, only occasionally you get lucky. If you are just trying your luck, then fair enough. You more usually probably need paid professional help for that, that is to say someone to do a lot of work for you, the bulk of which is usually trying to figure out what you want in the first place! I have no idea where you should go for that, like most people here, I am just an Excel hobbyist.
I see now that you have some very large formulas with, amongst other things, Index in them.
In most cases, VBA is a much better alternative than a lot of long formulas in a spreadsheet.
So you are thinking along sensible lines. If this was my project, then I likely would have VBA in place of a lot of the formulas, and my file would probably be shorter in size. You have the right idea I think.
Unfortunately I am not familiar enough with what is going on. Probably as I know nothing about American Football and how the scoring works.
I expect the coding would be quite simple to do, and certainly simplify greatly your spreadsheet. But I would need a week I expect to figure out what is going on and what you actually need doing.
I think you will need an Excel enthusiast that is also an American Football enthusiast. As far as I know, we don’t have anyone like that here.
Alternatively you will need to break down the thing into simple plain English terms explaining what each formula does.
Usually it is easier , ( at least that is what I find ) , to do a complicated thing in VBA then in a formula. Also, usually, a single macro would replace a long list of similar formulas.
So you are thinking along the right lines.
I am not near the computer much for a couple of days. I will have some more time in a few days. If you don’t get help anywhere else in the meantime, and want to give me a clear description of what actually a formula or two are doing, ( In simple English terms, not in American Football language ) , then I will try to give you a macro or two to do the same thing.
Good luck
Alan