Slow VBA Macro

steveh8204

Board Regular
Joined
Aug 20, 2018
Messages
143
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.

VBA Code:
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
 
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
 
Upvote 0

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
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.
 
Upvote 0
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 :)
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,293
Members
448,564
Latest member
ED38

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
Back
Top