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

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
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

D
  • Question
Running a VBA Subroutine resulted in a Runtime Error "13"
Replies
4
Views
440
DonEB
D
A
  • Question
runtime error 6 overflow, macro to create table
Replies
8
Views
299
atditiljazi
A
A
  • Solved
Calendar form cancel button passing data to worksheet
Replies
7
Views
380
AndyEd
A
M
  • Question
VBA Code - Run Code if cell value is not blank
Replies
0
Views
420
MHamid
M
E
  • Question
NBA Fantasy Spreadsheet formula to assign players to respective positions
Replies
0
Views
639
ExcelNoob123_098
E

Forum statistics

Threads
1,214,590
Messages
6,120,421
Members
448,961
Latest member
nzskater

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