nfernand
asked on
VBA, arrays and Consolidate
Hi guys... maybe this is an easy one.
I have this Macro in Excel VBA and it works:
--------------------------
Selection.Consolidate Sources:= _
Array("'Consolidado'!R10C1
"'Consolidado'!R18C1:R27C4
, Function:=xlSum, TopRow:=False, LeftColumn:=True, _
CreateLinks:=False
--------------------------
The "consolidate" command needs an array to especify the ranges to be consolidated. In this example there's an array of 2 elements.
And this is my problem: In runtime the array can be 1, 2, 3 o more elements, and I don't know how to build the array (in runtime of course). The "consolidate" command doesn't let me put an string array.
Thank you guys.
I have this Macro in Excel VBA and it works:
--------------------------
Selection.Consolidate Sources:= _
Array("'Consolidado'!R10C1
"'Consolidado'!R18C1:R27C4
, Function:=xlSum, TopRow:=False, LeftColumn:=True, _
CreateLinks:=False
--------------------------
The "consolidate" command needs an array to especify the ranges to be consolidated. In this example there's an array of 2 elements.
And this is my problem: In runtime the array can be 1, 2, 3 o more elements, and I don't know how to build the array (in runtime of course). The "consolidate" command doesn't let me put an string array.
Thank you guys.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi Cri and thank you for your answer.
As you ask me, I didn't reject your answer if it didn't works, and it didn't. According to your text I wrote this:
--------------------------
Dim vector() As string
ReDim vector(1)
vector(1) = "'Consolidado'!R10C1:R16C3
ReDim Preserve vector(UBound(vector) + 1)
vector(2) = "'Consolidado'!R18C1:R27C4
Range("F10").Select
Selection.Consolidate Sources:=vector _
, Function:=xlSum, TopRow:=False, _
LeftColumn:=True, CreateLinks:=False
--------------------------
I understand that "redim" can changes an array size, and it do it, but "consolidate" still gives me the same error: 1004 "Not a valid consolidation reference"
Mr. vbokhar
Thank you for your answer but it doens't look too useful, because I don't know how many elements the array may have.
As you ask me, I didn't reject your answer if it didn't works, and it didn't. According to your text I wrote this:
--------------------------
Dim vector() As string
ReDim vector(1)
vector(1) = "'Consolidado'!R10C1:R16C3
ReDim Preserve vector(UBound(vector) + 1)
vector(2) = "'Consolidado'!R18C1:R27C4
Range("F10").Select
Selection.Consolidate Sources:=vector _
, Function:=xlSum, TopRow:=False, _
LeftColumn:=True, CreateLinks:=False
--------------------------
I understand that "redim" can changes an array size, and it do it, but "consolidate" still gives me the same error: 1004 "Not a valid consolidation reference"
Mr. vbokhar
Thank you for your answer but it doens't look too useful, because I don't know how many elements the array may have.
nfernand,
Thank you for giving me the second chance I asked for. It is getting late, and I do not want to lock this question longer:
a) Why do you define the array as _string_ ? You pass the ranges as string, yes, but what do you want to consolidate ? I would expect as Single, Long, Currency, etc. or as Variant.
b) I think you are misusing the preserve command, see 'Note' in the help text of my first post.
I think vboukhar is on the right track and he knows more about VBA than I do. So if the a.m. did not help, reject my answer and continue with him. I bet you are finished before the weekend.
Best Regards
\cri
Thank you for giving me the second chance I asked for. It is getting late, and I do not want to lock this question longer:
a) Why do you define the array as _string_ ? You pass the ranges as string, yes, but what do you want to consolidate ? I would expect as Single, Long, Currency, etc. or as Variant.
b) I think you are misusing the preserve command, see 'Note' in the help text of my first post.
I think vboukhar is on the right track and he knows more about VBA than I do. So if the a.m. did not help, reject my answer and continue with him. I bet you are finished before the weekend.
Best Regards
\cri
nfernand!
Look at little changed code:
Sub ChangeSource()
Dim DimRan As Variant
DimRan = Array("d1!R1C1:R5C2", "d2!R1C1:R5C2")
Worksheets(1).Range("A1").
Sources:=DimRan, _
Function:=xlSum
' now consolidate data from three sheets:
ReDim Preserve DimRan(UBound(DimRan)) ' increase size
DimRan(UBound(DimRan) - 1) = "d3!R1C1:R5C2" ' add new item
Worksheets(1).Range("A1").
Sources:=DimRan, _
Function:=xlSum
End Sub
It's same thing, but with ReDim Preserve instead of redifinition of Array (in my previous comment). I meant, that if you create string with address of consolidated ranges in run-time, you can use this string either in Array definition or in ReDim and next assigment of added item of array.
I hope it helps.
Look at little changed code:
Sub ChangeSource()
Dim DimRan As Variant
DimRan = Array("d1!R1C1:R5C2", "d2!R1C1:R5C2")
Worksheets(1).Range("A1").
Sources:=DimRan, _
Function:=xlSum
' now consolidate data from three sheets:
ReDim Preserve DimRan(UBound(DimRan)) ' increase size
DimRan(UBound(DimRan) - 1) = "d3!R1C1:R5C2" ' add new item
Worksheets(1).Range("A1").
Sources:=DimRan, _
Function:=xlSum
End Sub
It's same thing, but with ReDim Preserve instead of redifinition of Array (in my previous comment). I meant, that if you create string with address of consolidated ranges in run-time, you can use this string either in Array definition or in ReDim and next assigment of added item of array.
I hope it helps.
nfernand,
1. The variable that should hold your array should be declared as a variant, not as a string.
2. The first element of an array is numbered 0 by default.
Redim Vector(1) actually makes the array two elements large. Vector(0) and Vector(1)
3. You don't need to use UBound if you know the number yourself.
Sub Helpnfernand()
Dim Vector
ReDim Vector(0)
Vector(0) = "'Consolidado'!R10C1:R16C3
ReDim Preserve Vector(1)
Vector(1) = "'Consolidado'!R18C1:R27C4
Selection.Consolidate _
Sources:=Vector, _
Function:=xlSum, _
TopRow:=False, _
LeftColumn:=True, _
CreateLinks:=False
End Sub
Ture Magnusson
Karlstad, Sweden
1. The variable that should hold your array should be declared as a variant, not as a string.
2. The first element of an array is numbered 0 by default.
Redim Vector(1) actually makes the array two elements large. Vector(0) and Vector(1)
3. You don't need to use UBound if you know the number yourself.
Sub Helpnfernand()
Dim Vector
ReDim Vector(0)
Vector(0) = "'Consolidado'!R10C1:R16C3
ReDim Preserve Vector(1)
Vector(1) = "'Consolidado'!R18C1:R27C4
Selection.Consolidate _
Sources:=Vector, _
Function:=xlSum, _
TopRow:=False, _
LeftColumn:=True, _
CreateLinks:=False
End Sub
Ture Magnusson
Karlstad, Sweden
You awarded this question to cri, but cri never got these points until I fix them today. This question has been stuck in a -1 userid condition and I found it and will fix this today. The expert will finally get these points now and the well deserved "A" grade. These problems happened in 1999 when we were having the database and jsp-500 errors. All fixed and your points verified.
Moondancer
Community Support Moderator @ Experts Exchange
Moondancer
Community Support Moderator @ Experts Exchange
It's easy indeed - see my sample below:
Sub ChangeSource()
Dim DimRan As Variant
DimRan = Array("d1!R1C1:R5C2", "d2!R1C1:R5C2")
Worksheets(1).Range("A1").
Sources:=DimRan, _
Function:=xlSum
' now consolidate data from three sheets:
DimRan = Array("d1!R1C1:R5C2", "d2!R1C1:R5C2", "d3!R1C1:R5C2")
Worksheets(1).Range("A1").
Sources:=DimRan, _
Function:=xlSum
End Sub
By the way, you don't need to use ReDim in this case - if you define variable as Valiant, it could contains reference to array of any dimentions. You have to use ReDim to change the number of dimentions of previously explicitly defined array:
Dim a(3) as Variant
..
ReDim a(5)
Hope it helps.