I keep getting the following error and I don't know why:
Runtime Error '1004':
Method range of object global Failed
Here is my code with the line causing the issue denoted with the *.
Any help would be greatly appreciated!
Runtime Error '1004':
Method range of object global Failed
Here is my code with the line causing the issue denoted with the *.
Code:
Sub Button1_Click()ActiveSheet.Range("AL2:AL399").Value = 0
ActiveSheet.Range("BH2:BP399").Value = ""
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Dim i As Integer
one = 1
two = 2
three = 3
four = 4
five = 5
thou = 50000
eight = 8
Dim variable As Integer
min_cell = Range("BG6").Value
max_cell = Range("BG7").Value
gamer = Range("BG10").Value
If gamer = 0 Then
spot = "BA3"
ElseIf gamer = 1 Then
spot = "BB3"
End If
lineup_quantity = Range("BG8").Value
Dim pg(1 To 200) As String
Dim sg(1 To 200) As String
Dim sf(1 To 200) As String
Dim pf(1 To 200) As String
Dim cen(1 To 200) As String
Dim gar(1 To 200) As String
Dim forw(1 To 200) As String
Dim util(1 To 200) As String
Dim counter As Integer
Dim count As Integer
max_value = 1000
lineup_number = 1
For count = lineup_number To lineup_quantity
Dim players(1 To 8) As String
Dim positions(1 To 8) As String
Dim cell_numbers(1 To 8)
Dim game(1 To 8) As String
track = 1
SolverReset
solverok setcell:=spot, maxminval:=1, bychange:="AL" + CStr(min_cell) + ":AL" + CStr(max_cell), engine:=2
SolverOptions Precision:=0.0000001
solveradd cellref:="AL" + CStr(min_cell) + ":AL" + CStr(max_cell), relation:=5
solveradd cellref:=spot, relation:=1, formulatext:=max_value
solveradd cellref:="BB6:BB10", relation:=3, formulatext:=one
solveradd cellref:="BA2", relation:=1, formulatext:=thou
solveradd cellref:="BB6:BB9", relation:=1, formulatext:=three
solveradd cellref:="BB10", relation:=1, formulatext:=two
solveradd cellref:="BA14", relation:=2, formulatext:=eight
solveradd cellref:="BB11", relation:=1, formulatext:=four
solveradd cellref:="BB16", relation:=1, formulatext:=four
solveradd cellref:="BB12", relation:=1, formulatext:=five
solversolve userfinish:=True
If gamer = 0 Then
max_value = Range(spot).Value - 0.001
Range("BP" + CStr(count + 1)).Value = max_value + 0.001
End If
For counter = min_cell To max_cell
If Range("AL" + CStr(counter)).Value = 1 Then
players(track) = Range("B" + CStr(counter)).Value
positions(track) = Range("A" + CStr(counter)).Value
cell_numbers(track) = counter
track = track + 1
End If
Next counter
For counter = 1 To 8
With ThisWorkbook.Worksheets("Predictions")
********game(counter) = Range("D" + CStr(cell_numbers(counter))).Value *********
End With
Next counter
If game(1) = game(2) And game(1) = game(3) And game(1) = game(4) And game(1) = game(5) And game(1) = game(6) And game(1) = game(7) And game(1) = game(8) Then
count = count - 1
GoTo Line
End If
For counter = 1 To 8
If positions(counter) = "PG" Then
If Range("BH" + CStr(count + 1)).Value = "" Then
Range("BH" + CStr(count + 1)).Value = players(counter)
ElseIf Range("BM" + CStr(count + 1)).Value = "" Then
Range("BM" + CStr(count + 1)).Value = players(counter)
ElseIf Range("BO" + CStr(count + 1)).Value = "" Then
Range("BO" + CStr(count + 1)).Value = players(counter)
End If
End If
Next counter
For counter = 1 To 8
If positions(counter) = "SG" Then
If Range("BI" + CStr(count + 1)).Value = "" Then
Range("BI" + CStr(count + 1)).Value = players(counter)
ElseIf Range("BM" + CStr(count + 1)).Value = "" Then
Range("BM" + CStr(count + 1)).Value = players(counter)
ElseIf Range("BO" + CStr(count + 1)).Value = "" Then
Range("BO" + CStr(count + 1)).Value = players(counter)
End If
End If
Next counter
For counter = 1 To 8
If positions(counter) = "SF" Then
If Range("BJ" + CStr(count + 1)).Value = "" Then
Range("BJ" + CStr(count + 1)).Value = players(counter)
ElseIf Range("BN" + CStr(count + 1)).Value = "" Then
Range("BN" + CStr(count + 1)).Value = players(counter)
ElseIf Range("BO" + CStr(count + 1)).Value = "" Then
Range("BO" + CStr(count + 1)).Value = players(counter)
End If
End If
Next counter
For counter = 1 To 8
If positions(counter) = "PF" Then
If Range("BK" + CStr(count + 1)).Value = "" Then
Range("BK" + CStr(count + 1)).Value = players(counter)
ElseIf Range("BN" + CStr(count + 1)).Value = "" Then
Range("BN" + CStr(count + 1)).Value = players(counter)
ElseIf Range("BO" + CStr(count + 1)).Value = "" Then
Range("BO" + CStr(count + 1)).Value = players(counter)
End If
End If
Next counter
For counter = 1 To 8
If positions(counter) = "C" Then
If Range("BL" + CStr(count + 1)).Value = "" Then
Range("BL" + CStr(count + 1)).Value = players(counter)
ElseIf Range("BO" + CStr(count + 1)).Value = "" Then
Range("BO" + CStr(count + 1)).Value = players(counter)
End If
End If
Next counter
For counter = 1 To 8
If positions(counter) = "PG/SG" Then
If Range("BH" + CStr(count + 1)).Value = "" Then
Range("BH" + CStr(count + 1)).Value = players(counter)
ElseIf Range("BI" + CStr(count + 1)).Value = "" Then
Range("BI" + CStr(count + 1)).Value = players(counter)
ElseIf Range("BM" + CStr(count + 1)).Value = "" Then
Range("BM" + CStr(count + 1)).Value = players(counter)
ElseIf Range("BO" + CStr(count + 1)).Value = "" Then
Range("BO" + CStr(count + 1)).Value = players(counter)
End If
End If
Next counter
For counter = 1 To 8
If positions(counter) = "PF/C" Then
If Range("BL" + CStr(count + 1)).Value = "" Then
Range("BL" + CStr(count + 1)).Value = players(counter)
ElseIf Range("BK" + CStr(count + 1)).Value = "" Then
Range("BK" + CStr(count + 1)).Value = players(counter)
ElseIf Range("BN" + CStr(count + 1)).Value = "" Then
Range("BN" + CStr(count + 1)).Value = players(counter)
ElseIf Range("BO" + CStr(count + 1)).Value = "" Then
Range("BO" + CStr(count + 1)).Value = players(counter)
End If
End If
Next counter
For counter = 1 To 8
If positions(counter) = "SF/PF" Then
If Range("BK" + CStr(count + 1)).Value = "" Then
Range("BK" + CStr(count + 1)).Value = players(counter)
ElseIf Range("BJ" + CStr(count + 1)).Value = "" Then
Range("BJ" + CStr(count + 1)).Value = players(counter)
ElseIf Range("BN" + CStr(count + 1)).Value = "" Then
Range("BN" + CStr(count + 1)).Value = players(counter)
ElseIf Range("BO" + CStr(count + 1)).Value = "" Then
Range("BO" + CStr(count + 1)).Value = players(counter)
End If
End If
Next counter
For counter = 1 To 8
If positions(counter) = "SG/SF" Then
If Range("BI" + CStr(count + 1)).Value = "" Then
Range("BI" + CStr(count + 1)).Value = players(counter)
ElseIf Range("BJ" + CStr(count + 1)).Value = "" Then
Range("BJ" + CStr(count + 1)).Value = players(counter)
ElseIf Range("BM" + CStr(count + 1)).Value = "" Then
Range("BM" + CStr(count + 1)).Value = players(counter)
ElseIf Range("BN" + CStr(count + 1)).Value = "" Then
Range("BN" + CStr(count + 1)).Value = players(counter)
ElseIf Range("BO" + CStr(count + 1)).Value = "" Then
Range("BO" + CStr(count + 1)).Value = players(counter)
End If
End If
Next counter
For counter = 1 To 8
If positions(counter) = "PG/SF" Then
If Range("BH" + CStr(count + 1)).Value = "" Then
Range("BH" + CStr(count + 1)).Value = players(counter)
ElseIf Range("BJ" + CStr(count + 1)).Value = "" Then
Range("BJ" + CStr(count + 1)).Value = players(counter)
ElseIf Range("BM" + CStr(count + 1)).Value = "" Then
Range("BM" + CStr(count + 1)).Value = players(counter)
ElseIf Range("BN" + CStr(count + 1)).Value = "" Then
Range("BN" + CStr(count + 1)).Value = players(counter)
ElseIf Range("BO" + CStr(count + 1)).Value = "" Then
Range("BO" + CStr(count + 1)).Value = players(counter)
End If
End If
Next counter
For counter = 1 To lineup_number
If players(1) = pg(counter) And players(2) = sg(counter) And players(3) = sf(counter) And players(4) = pf(counter) And players(5) = cen(counter) And players(6) = gar(counter) And players(7) = forw(counter) And players(8) = util(counter) Then
count = count - 1
GoTo Line
End If
Next counter
pg(lineup_number) = players(1)
sg(lineup_number) = players(2)
sf(lineup_number) = players(3)
pf(lineup_number) = players(4)
cen(lineup_number) = players(5)
gar(lineup_number) = players(6)
forw(lineup_number) = players(7)
util(lineup_number) = players(8)
Line:
Worksheets("Predictions").UsedRange.Columns("AI:AI").Calculate
Next count
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub
Any help would be greatly appreciated!