CODEWITHSUNDEEP
excelvba
user3391013
user3391013

Reputation: 17

Adding values to an array in vba

I have an array in vba in excel. This array is a set size, it doesnt need to be variable.

For the life of me I don't know what I'm doing wrong in trying to add values to the array. My code as it currently stands is :

Dim count As Integer
Dim name(１ to 361) As String
Dim tmp As String

Sub go()

For count = 2 To 10
    name(count) = cells(7,count)
Next count

End Sub

I've tried all manner of variations on this. cells(7,count).value, name(count).value, etc... etc...

But I keep getting errors telling me I'm missing an as or there's a generic syntax error.

Its really annoying to be falling at this first hurdle before i can even begin to do anything useful with the array. This is pushing me to finding an arrayless solution but that would be working around the problem rather than actually tackling it.

Upvotes: 1

Views: 3928

Answers (2)

John Alexiou
John Alexiou

Reputation: 29264

I suggest using a dynamic array assignment

Sub go()
    Dim i as Long, N as Long
    Dim names() as Variant
    ' It will be initialized to names(1 to rows, 1 to columns)

    N = 10
    names = Range("RefToFirstCell").Resize(1,N).Value2

    For i=1 to N
        Debug.Print names(1,i)
    Next i

    'If you want to write back to the worksheet use `Range().Resize().Value2 = names`.

End Sub

Upvotes: 1

chris neilsen
chris neilsen

Reputation: 53136

You first issue is that go is a reserved word. Choose another name from your Sub. Same applies to name. There are also several other issues, see below

' Dont use key words as procedure names
Sub MySub()
    ' Declare as procedure scope
    '  unless there is a very good reason to use module scope

    ' dont use key words as variable names
    Dim cnt As Long ' Integer ' use Long by default
    Dim nm(1 To 361) As String
    Dim tmp As String

    For cnt = 2 To 10
        nm(cnt) = Cells(7, cnt)
    Next cnt
End Sub

Upvotes: 1

Related Questions