variants and arrays

[maks25]

Hello,

I am trying to loop through sheets and combine columns of data into a variant

dim sSheet(3) as string
dim mgTickers() as variant
dim x as integer

Do
Sheets(sSheet(x)).Select


'variant
With ActiveSheet
    mgTickers() = Range(.Cells(22, 1), .Cells(.Rows.Count, 1).End(xlDown)).Value
End With


x = x + 1
Loop Until x > 3

Obviously the code above would just replace the variant data with the new data from the next sheet. My question is, how can I add data to the variant without overwriting it?

Thank you.

 

[JoeMo]

Hello,

I am trying to loop through sheets and combine columns of data into a variant

dim sSheet(3) as string
dim mgTickers() as variant
dim x as integer

Do
Sheets(sSheet(x)).Select


'variant
With ActiveSheet
    mgTickers() = Range(.Cells(22, 1), .Cells(.Rows.Count, 1).End(xlDown)).Value
End With


x = x + 1
Loop Until x > 3

Obviously the code above would just replace the variant data with the new data from the next sheet. My question is, how can I add data to the variant without overwriting it?

Thank you.

I would use a second variant (array) like this:

Sub VariantArray()
Dim sSheet(3) As String
Dim vA As Variant
Dim mgTickers() As Variant
Dim x As Integer, i As integr, j As Integer

ReDim mgTickers(1 To 1)
'Need to establish values for sSheet() here
Do
Sheets(sSheet(x)).Select
'variant
With ActiveSheet
    vA = Range(.Cells(22, 1), .Cells(.Rows.Count, 1).End(xlDown)).Value
    For i = LBound(vA, 1) To UBound(vA, 1)
        ReDim Preserve mgTickers(1 To UBound(mgTickers) + UBound(vA, 1) - 1)
        j = j + 1
        mgTickers(j) = vA(i, 1)
    Next i
End With
x = x + 1
Loop Until x > 3
End Sub

 

[maks25]

I would use a second variant (array) like this:

Sub VariantArray()
Dim sSheet(3) As String
Dim vA As Variant
Dim mgTickers() As Variant
Dim x As Integer, i As integr, j As Integer

ReDim mgTickers(1 To 1)
'Need to establish values for sSheet() here
Do
Sheets(sSheet(x)).Select
'variant
With ActiveSheet
    vA = Range(.Cells(22, 1), .Cells(.Rows.Count, 1).End(xlDown)).Value
    For i = LBound(vA, 1) To UBound(vA, 1)
        ReDim Preserve mgTickers(1 To UBound(mgTickers) + UBound(vA, 1) - 1)
        j = j + 1
        mgTickers(j) = vA(i, 1)
    Next i
End With
x = x + 1
Loop Until x > 3
End Sub

Thanks,

I tried it however I got an overflow error, so I changed i to a long.

But now I am getting an "Out of memory error" on

ReDim Preserve mgTickers(1 To UBound(mgTickers) + UBound(vA, 1) - 1)

How can I fix it?

thanks for helping me out!

Max

 

[maks25]

Found the error

vA = Range(.Cells(22, 1), .Cells(.Rows.Count, 1).End(xlDown)).Value

fixed it:

vA = Range(.Cells(22, 1), .Cells(22,1).End(xlDown)).Value

Now it works like a charm!

Thanks

 

[JoeMo]

Thanks,

I tried it however I got an overflow error, so I changed i to a long.

But now I am getting an "Out of memory error" on


How can I fix it?

thanks for helping me out!

Max

Maybe too much data in the columns you are trying to combine. Try it in a reduced-size test to see if it is working first. If it is then reply with an explanation of what you are trying to achieve and maybe a better approach can be found.