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