CODEWITHSUNDEEP
arraysexcelvba
user2780563
user2780563

Reputation: 11

How to assign excel cell with string to array value in visual basic?

I'm pretty new to visual basic, but I'm having trouble assigning cell values to members of an array. Basically what I am doing is looping through a column and assigning each cell to a new part of the array. Test code is posted below:

Sub Test()
    Dim List(5) As String
    Dim celltext As String

    For i = 1 To 5
        celltxt = ActiveSheet.Range("K" & i).Text
        List(i) = celltext
        MsgBox List(i)
    Next i
End Sub

Each time the message box pops up though, it is blank, meaning that the assignment didn't work. The cells at those coordinates DO have values. Any clues?

Upvotes: 0

Views: 10808

Answers (3)

user2140261
user2140261

Reputation: 7993

You might also with to try: 

Dim List As Variant
Dim i As Long

List = ActiveSheet.Range("K1:K5")

For i = 1 To UBound(List)
    MsgBox List(i, 1)
Next i

This will add performance by only reading from the worksheet once instead of each time the loop is looped.

Upvotes: 1

Siddharth Rout
Siddharth Rout

Reputation: 149305

  1. When you Dim List(5) As String. The lowest element in the array is 0 and not 1. You might want to change that to Dim List(1 to 5) As String else your first element will always be blank in the array.

  2. You are using ActiveSheet. Are you sure it is the right sheet?

Try this code

Sub Test()
    Dim List(1 To 5) As String
    Dim ws As Worksheet
    Dim i as Long

    Set ws = ThisWorkbook.Sheets("Sheet1")

    For i = 1 To 5
        List(i) = ws.Range("K" & i).Value
        MsgBox List(i)
    Next i
End Sub

Upvotes: 1

Joe
Joe

Reputation: 6827

You are assigning to "celltxt" but reading from "celltext".

Add Option Explicit at the top of every module -- that will make these types of errors more obvious.

Upvotes: 2

Related Questions