CODEWITHSUNDEEP
vbaexcelvariant
msmf14
msmf14

Reputation: 1499

Extracting Data from Excel Sheet to VBA: Empty Variant/Array, but UBound Returns Number

I am trying to extract text data from an Excel sheet into an array (defined as a variant in this case).

The below code does not return desirable results: When I try to access an element in the SearchItems variant array, an error pops up saying subscript out of range.

However when I run UBound(SearchItems) the system returns the value of LR (instead of LR-1?).

In any case does that indicate that data if already loaded onto the array?

Sub Testing()

Dim SearchItems As Variant
Dim LR As Integer

LR = Sheets("MySheet").Cells(Rows.Count, "A").End(xlUp).Row 'Get number of cells in column A

SearchItems = Sheets("MySheet").Range("A1:A" & LR).Value

End Sub

Upvotes: 0

Views: 2410

Answers (2)

Patrick Lepelletier
Patrick Lepelletier

Reputation: 1654

the array searchitems starts from 0, so of course ubound will add +1 to the size u think it has.

If you need Ubound to work (as the tittle of the post suggests) :

Sub Testing()
Dim SearchItems() As Variant 'we want SeachItems to be a dynamic array
Dim LR As Long, i As Long

with Sheets("MySheet")
    LR = .Cells(.Rows.Count, 1).End(xlUp).Row 'an other way of Getting the number of cells in column A, note the '.' before rows
    redim SearchItems ( 1 to LR, 1 to 1) ' this way ubound should work
    SearchItems = .Range(.cells(1,1) , .cells(LR,1) ).Value 'an other way of doing it (strangely faster considering its bigger code, tested it)
end with

For i = 1 To LR 'or to Ubound (SearchItems)
    'do stuff with  SearchItems(i, 1) 
Next i


'to write it back to the worksheet :
Sheets("MySheet").Range("A1:A" & LR).Value = SearchItems

End Sub

Upvotes: 0

Gary's Student
Gary's Student

Reputation: 96753

You are dealing with a two dimensional array:

Sub Testing()
    Dim SearchItems As Variant
    Dim LR As Integer, i As Integer
    LR = Sheets("MySheet").Cells(Rows.Count, "A").End(xlUp).Row 'Get number of cells in column A
    SearchItems = Sheets("MySheet").Range("A1:A" & LR).Value
    For i = 1 To LR
        MsgBox SearchItems(i, 1)
    Next i
End Sub

Upvotes: 2

Related Questions