Runtime Error '1004'

B

bballboy8

New Member
Joined
Jan 23, 2018
Messages
1
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 *.
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!
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Sort by date Sort by votes
What is the value of cell_numbers(counter) when you get the error?
Also is that range on the active sheet or the Predictions sheet?
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question Question
Runtime Error 91
2
Replies
15
Views
637
rtr1811
R
D
  • Question Question
Running a VBA Subroutine resulted in a Runtime Error "13"
Replies
4
Views
614
DonEB
D
A
  • Question Question
runtime error 6 overflow, macro to create table
Replies
8
Views
534
atditiljazi
A
P
  • Solved
Need help with VBA code prompting Compile error: Next without For
2
Replies
15
Views
481
Pi_Lover
P
A
  • Solved
Calendar form cancel button passing data to worksheet
Replies
7
Views
1K
AndyEd
A

Forum statistics

Threads
1,223,888
Messages
6,175,217
Members
452,619
Latest member
Shiv1198

Share this page

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