Slow VBA Macro

[steveh8204]

I've made a macro in vba which is taking quite a bit of time to run (approx 70 secs) and was wondering if there's any way to make it run more efficiently.

It basically just copies and pastes a couple of values, pastes them in another sheet then extracts about a dozen values, switches the sheet then pastes them values in. It then repeats this process up to 12 times.

There's a few formulas as well in the final output sheet but can't imagine these hold it up much. The macro code is below if anyone wants to have a look. Any tips on how to speed it up would be appreciated.

Thanks in advance.

Sub games_batch_compare()

Range("C21").Value = "Calculating...."

Application.ScreenUpdating = False

Dim a As Variant, b As Variant, c As Variant, d As Variant, e As Variant, f As Variant, g As Variant, h As Variant
Dim i As Integer, j As Variant, k As Variant, l As Variant, m As Variant, n As Variant, o As Variant, league As Variant

' make sure selected league from top left matches "SELECT LEAGUE"

league = Range("D1")

Worksheets("SELECT LEAGUE").Select

Cells(2, 5).Value = league

Worksheets("Games").Select ' back to "Games" sheet


For i = 3 To 15

a = Cells(i, 3) ' selected Home Team into variable a
b = Cells(i, 5) ' selected Away team into variable b

If a = "" Then i = 15

Worksheets("H2H").Select

Range("L3").Value = a ' change H2H Home team to variable a
Range("S3").Value = b ' change H2H Away team to variable b
c = Range("L17") ' declare as home team goals scored
d = Range("s18") ' declare as away team Goals scored
e = Range("N17") ' declare as home team Goals conceded
f = Range("U18") ' declare as away team Goals conceded
g = Range("n46") ' declare as clean sheets home
h = Range("n52") ' declare as failed to score Home
j = Range("u47") ' declare as clean sheets away
k = Range("u53") ' declare as failed to score away
l = Range("l11") ' declare as home position Overall
m = Range("l12") ' declare as home position home
n = Range("s13") ' declare as away position away
o = Range("s11") ' declare as away position overall
p = Range("q206") ' declare as home team points last 4
q = Range("Z207") ' declare as away team points last 4

If a = "" Then c = "N/A"
If a = "" Then d = "N/A"
If a = "" Then e = "N/A"
If a = "" Then d = "N/A"
If a = "" Then f = "N/A"
If a = "" Then g = "N/A"
If a = "" Then h = "N/A"
If a = "" Then j = "N/A"
If a = "" Then k = "N/A"
If a = "" Then l = "N/A"
If a = "" Then m = "N/A"
If a = "" Then n = "N/A"
If a = "" Then o = "N/A"

Worksheets("Games").Select

Cells(i, 7).Value = c ' goals scored H
Cells(i, 8).Value = d ' goals scored A
Cells(i, 10).Value = e ' goals conceded H
Cells(i, 11).Value = f ' goals conceded A
Cells(i, 13).Value = g ' clean sheets home
Cells(i, 14).Value = h ' failed to score Home
Cells(i, 15).Value = j ' clean sheets away
Cells(i, 16).Value = k ' failed to score Away
Cells(i, 17).Value = l ' home position overall
Cells(i, 18).Value = m ' home position home
Cells(i, 19).Value = n ' away position away
Cells(i, 20).Value = o ' away position overall
Cells(i, 26).Value = p ' home team points last 4
Cells(i, 27).Value = q ' away team points last 4

Next i

Application.ScreenUpdating = True

Range("C21").Value = "Complete!"

End Sub

 

[johnnyL]

Ok, this is a rabbit hole that I am no longer going to pursue. I am not going to rewrite the entire program.

This Is what I found during my initial check in case anyone else cares to pursuit this:

Games sheet
-----------
Games C3 = First Home Team
'
Games E3 = First Away Team
'
Games G3 = First Home Goals Scored
Games H3 = First Away Goals Scored
'
Games J3 = First Home Goals Conceded
Games K3 = First Away Goals Conceded
'
Games M3 = First Home CS
Games N3 = First Home FTS
Games O3 = First Away CS
Games P3 = First Away FTS
Games Q3 = First Overall H Position
Games R3 = First Home Position
Games S3 = First Away Position
Games T3 = First Overall A Position
'
Games Z3 = First Last 4 Home
Games AA3 = First Last 4 Away
Games AB3 = First Home Pts Last 8 Total
Games AC3 = First Away Pts Last 8 Total
'
'
Games Sheet Formulas:
---------------------
Games D1 ='SELECT LEAGUE'!B5
'
Games I3 = First Home + away Goals Scored Total =IF(G3=0,"N/A",IF(G3="N/A","N/A",SUM(G3:H3)))
Games L3 = First Home + away Goals Conceded Total =IF(G3=0,"N/A",IF(G3="N/A","N/A",SUM(J3:K3)))
'
Games U3 = First Position Diff =IF(G3=0, -100, IF(G3="N/A", "N/A", S3-R3))
Games V3 = First Avg Goals =IF(G3=0,0,IF(G3="N/A","N/A",AVERAGE(I3,L3)))
Games W3 = First Likely Score 1 =IF(G3="N/A",0, IF(G3="",0, AVERAGE(G3,K3)))
Games X3 = First Likely Score 2 =IF(G3=0,"", IF(G3="", 0, AVERAGE(H3,J3)))
'
Games AD3 = First "+/-" =SUM((AB3-AC3)+(Z3-AA3))
'
'------------------------------------------------------------------------------------------------------
'
H2H sheet
---------
L3 is a drop down list of teams
S3 is a drop down list of teams
'
L6 = Total Matches Played =VLOOKUP($L$3,'Main Table'!$B$60:$Z$83,2,FALSE)
M6 = Total Matches Won =VLOOKUP($L$3,'Main Table'!$B$60:$Z$83,3,FALSE)
N6 = Total Matches Drawn =VLOOKUP($L$3,'Main Table'!$B$60:$Z$83,4,FALSE)
O6 = Total Matches Lost =VLOOKUP($L$3,'Main Table'!$B$60:$Z$83,5,FALSE)
P6 = Total Goal Scored =VLOOKUP($L$3,'Main Table'!$B$60:$Z$83,6,FALSE)
Q6 = Total Goal Conceded =VLOOKUP($L$3,'Main Table'!$B$60:$Z$83,7,FALSE)
etc ...
'
'------------------------------------------------------------------------------------------------------
'
Select League sheet
-------------------
B5 is a drop down list of Leagues

 

[steveh8204]

Ok, this is a rabbit hole that I am no longer going to pursue. I am not going to rewrite the entire program.

Ok, no need to be so curt, you asked for the source code.

 

[johnnyL]

Sorry @steveh8204. I should have been more clear. My intention was to redo it from the ground up, but it is far more complex than originally envisioned it being.

I am still going to look into it though for you.

 

[steveh8204]

Sorry @steveh8204. I should have been more clear. My intention was to redo it from the ground up, but it is far more complex than originally envisioned it being.

I am still going to look into it though for you.

Ah no worries, I understand I've opened a can of worms lol. Your help has been appreciated though

 

[johnnyL]

Just to provide some further info on the rabbit hole I mentioned. There are over 456,000 formulas in that Betting-Data file.

Question #1: Are either of those two excel files replaced very often? Just trying to figure out which approach I might look into next as far as your current necessity to have both of the files open.

Question #2: Do you actually do anything in that additional file that currently needs to be opened? Or does it just need to be opened.

 

[steveh8204]

The main one I use is pretty static (except the data it imports) however I do tweak it quite a bit just to make it look better or provide extra functionality.

The second sheet that holds the data is updated a few times a week, a new one is released quite often which just holds the extra data from the latest games.

456, 000 formulas, wow. Knew there was a lot going on in the background.

Thanks for your ongoing help, it is hugely appreciated and beyond what I expected. With the data handling I'm pretty much expecting not to get anymore efficiency gains so its fine if you've got other things you need to do although as I said before any assistance is appreciated a lot.