CODEWITHSUNDEEP
vbaexcelvariablesdata-structures
Clemens
Clemens

Reputation: 245

read variable excel data into variant with the same format

I'm currently writing a Macro for Excel. First I want to read my variable settings from row 20 from my sheet "Filter" into my variable "test": 

' Define Last Column with a value
LastCol = Sheets("Filter").Cells(20, Sheets("Filter").Columns.Count).End(xlToLeft).Column
Col_Letter = Split(Cells(1, LastCol).Address(True, False), "$")(0)

' Read Data into variable
test = Sheets("Filter").Range("B20:" & Col_Letter & "20").Value

This works fine so far and I get a variant with a structure like that: 

test
  test(1)
    test(1,1) = value1
    test(1,2) = value2
    test(1,3) = value3
    ...

My problem comes now: When I have only one value to read, the structure changes automatically to following: 

test = value1

How can I change the structure to this?

test
  test(1)
    test(1,1) = value1

My problem is that with this one variable I run into various problems in my code (ubound, lbound, ...).

Upvotes: 0

Views: 743

Answers (2)

Mohamed Allam
Mohamed Allam

Reputation: 81

You can check the length .. Try the following code:

Dim test As Variant
test = Sheets("Filter").Range("B20:" & Col_Letter & "20").Value
If Len(test) = 1 Then
        ReDim test(1, 1)
        test(1, 1) = Sheets("Filter").Range("B20:" & Col_Letter & "20").Value
End If

Upvotes: 2

R3uK
R3uK

Reputation: 14537

You could force test to be an array with only one cell, if the last column is B or less :

' Define Last Column with a value
LastCol = Sheets("Filter").Cells(20, Sheets("Filter").Columns.Count).End(xlToLeft).Column
Col_Letter = Split(Cells(1, LastCol).Address(True, False), "$")(0)

If LastCol <= 2 Then
    ReDim test(1 To 1, 1 To 1)
    test(1, 1) = Sheets("Filter").Range("B20").Value
Else
    'Read Data into variable
    test = Sheets("Filter").Range("B20:" & Col_Letter & "20").Value
End If

Upvotes: 2

Related Questions