VBA Array and String error

Question

Getting a "range has no values" error here.

I am trying to make an auto score for an excel bracket, and run a loop so if

winner(i) = guess(i)
points(i) = true
pointSum+=pointSum..

playing around with it now that's why everything only goes to 12.

Sub foo()

    Dim Player(1 - 12) As String
        Player(1) = Worksheets("Sheet1").Cells("M2")
        Player(2) = Worksheets("Sheet1").Cells("M4")
        Player(3) = Worksheets("Sheet1").Cells("M10")
        Player(4) = Worksheets("Sheet1").Cells("M12")
        Player(5) = Worksheets("Sheet1").Cells("M22")
        Player(6) = Worksheets("Sheet1").Cells("M24")
        Player(7) = Worksheets("Sheet1").Cells("M32")
        Player(8) = Worksheets("Sheet1").Cells("M34")

        Player(9) = Worksheets("Sheet1").cell("L1")
        Player(10) = Worksheets("Sheet1").cell("L3")
        Player(11) = Worksheets("Sheet1").cell("L5")
        Player(12) = Worksheets("Sheet1").cell("L7")


        Player(13) = Range("L9").Value
        Player(14) = Range("L11").Value
        Player(15) = Range("L13").Value
        Player(16) = Range("L15").Value
        Player(17) = Range("L20").Value
        Player(18) = Range("L22").Value
        Player(19) = Range("L24").Value
        Player(20) = Range("L26").Value
        Player(21) = Range("L28").Value
        Player(22) = Range("L30").Value
        Player(23) = Range("L32").Value
        Player(24) = Range("L34").Value

        Player(25) = Range("K2").Value
        Player(26) = Range("K6").Value
        Player(27) = Range("K10").Value
        Player(28) = Range("K14").Value
        Player(29) = Range("K21").Value
        Player(30) = Range("K25").Value
        Player(31) = Range("K29").Value
        Player(32) = Range("J4").Value

        Player(33) = Range("J12").Value
        Player(34) = Range("J23").Value
        Player(35) = Range("J31").Value

    Dim Winner(1 - 12) As String

        Winner(1) = "Mohler"
        Winner(2) = "Scotter"
        Winner(3) = "DKGAY"
        Winner(4) = "Lassie"

        Winner(5) = "Mohler"
        Winner(6) = "Gunnar"
        Winner(7) = "Gord'n"
        Winner(8) = "Hellmers"
        Winner(9) = "Evan Brown"
        Winner(10) = "Jerru"
        Winner(11) = "Case"
        Winner(12) = "Lassie"

        Winner(13) = "Mohler"
        Winner(14) = ""
        Winner(15) = ""
        Winner(16) = "Mohler"
        Winner(17) = "Mohler"
        Winner(18) = ""

    Dim Guess(1 - 12) As String

        Guess(1) = Player(10)
        Guess(2) = Player(14)
        Guess(3) = Player(18)
        Guess(4) = Player(23)

        Guess(5) = Player(25)
        Guess(6) = Player(26)
        Guess(7) = Player(27)
        Guess(8) = Player(28)
        Guess(9) = Player(29)
        Guess(10) = Player(30)
        Guess(11) = Player(31)
        Guess(12) = Player(32)

        Guess (13)
        Guess (14)
        Guess (15)
        Guess (16)
        Guess (17)
        Guess (18)

    Dim points(1 - 12) As Boolean
        points (1)
        points (2)
        points (3)
        points (4)

        points (5)
        points (6)
        points (7)
        points (8)
        points (9)
        points (10)
        points (11)
        points (12)

        points (13)
        points (14)
        points (15)
        points (16)
        points (17)
        points (18)


    Dim pointSum As Double

    Dim pointValue(1 - 6) As Double
        pointValue(1) = 10
        pointValue(2) = 20
        pointValue(3) = 30
        pointValue(4) = 40
        pointValue(5) = 50
        pointValue(6) = 60

    For i = 1 To 12 Step 1
        If Guess(i) = Winner(i) Then
            points(i) = True
        Else
            points(i) = False
        Next
    Loop Until points(i) = True Or points(i) = False
End Sub

brettdj · Accepted Answer · 2016-04-14 02:41:01Z

2

I would reconsider how you load your arrays.

If you had a contiguous range you could add it directly to an array, as your is not contiguous suggest you try something like this (so you tweak a range rather than individual array elements.)

Dim rng1 As Range
Dim rng2 As Range
Dim lngCnt As Long
Dim Player() As String

Set rng1 = Sheets(1).Range("M2,M4,M6,M10")
ReDim Player(1 To rng1.Cells.Count)
For Each rng2 In rng1
lngCnt = lngCnt + 1
 Player(lngCnt) = rng2.Value
Next

answered Apr 14, 2016 at 2:41

brettdj

55.9k16 gold badges120 silver badges178 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ron Rosenfeld · Accepted Answer · 2016-04-14 00:21:55Z

1

Bad syntax. Try:

Dim Player(1 to 12) As String

answered Apr 14, 2016 at 0:21

Ron Rosenfeld

62k7 gold badges35 silver badges71 bronze badges

Collectives™ on Stack Overflow

VBA Array and String error

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related