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

Question

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?

Joe · Accepted Answer · 2013-11-05 18:00:08Z

2

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.

answered Nov 5, 2013 at 18:00

Joe

6,8271 gold badge18 silver badges29 bronze badges

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

1 Comment

Siddharth Rout Over a year ago

+ 1 on Option Explicit

Siddharth Rout · Accepted Answer · 2013-11-05 18:07:06Z

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.
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

edited Nov 5, 2013 at 18:07

answered Nov 5, 2013 at 18:00

Siddharth Rout

150k18 gold badges212 silver badges258 bronze badges

Comments

user2140261 · Accepted Answer · 2013-11-05 18:12:53Z

1

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.

answered Nov 5, 2013 at 18:12

user2140261

8,0037 gold badges35 silver badges45 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related