Sorry you could not follow the code
I have managed to do what I wanted, but I loop through all the cells in each range, and this is not really very efficient.
If you understand the setting described below, could you advise me on how you would do it.
Do you think using arrays would be more efficient than looping through all the cells within each range. if yes how could I do it using arrays.
If the setting is not clear, do not worry. Your help is always greatly appreciated.
I have 11 worksheets (scenario 1 to 10 and "All scenarios")with 4 ranges in each. The range have the same name on all sheets: Rng1, Rng2, Rng3, Rng4.
I have a form allowing the user to decide which scenario to add. The idea is that the sum of the selected scenario is displayed in "total scenarios".
So for example if the user decides to sum Scenario1 and Scenario3:
(all scenario)Rng1=(Scenario1)Rng1 +(Scenario3)Rng1
(all scenario)Rng2=(Scenario1)Rng2 +(Scenario3)Rng2
(all scenario)Rng3=(Scenario1)Rng3 +(Scenario3)Rng3
(all scenario)Rng4=(Scenario1)Rng4 +(Scenario3)Rng4
looping through an array is the same as looping through a cell - in fact you can generally write code that can be passed a range or passed and array and the same code can be used to process it. That said, looping through an array is a lot faster than looping through a range.
Dim r As Range, v As Variant
Set r = Range("A1:J10")
v = r.Value
Public Function Process(vv As Variant)
tot = 0
For i = 1 To 10
For j = 1 To 10
tot = tot + vv(i, j)
MsgBox TypeName(vv) & ": " & tot
but it sounds to me like your rng1 is a rectangular range on every sheet and also on the "all scenario" or "total scenarios" sheet (whichever the actual name is). If that is the case, then each cell is a sum of that individual cell on each of the selected sheets. So that would require looping and if you pick up the values with a single statement as I show, then yes looping through an array would be faster.
Here is some pseudo code representing how you might loop through each cell in a range in only the selected sheets to accumulate the total for each cell for the same range in the summary sheet.
Dim v(1 to 10) as String
v(1) = "Scenario1"
v(2) = "Scenario2"
. . .
v(9) = "Scenario9"
v(10) = "Scenario10"
' this simulates an array built from your user choices in your form
' you would actually write code to pick up a list of whether the
' sheets are selected or not
Dim vSelect(1 to 10) as Boolean
Dim i as long, j as long
for i = 1 to 10
vSelect(i) = False
vSelect(1) = True
vSelect(2) = True
' Now your code to build that array has ended and we
' Now build a subset of selected sheet names
Dim vSelected as Variant
Dim vSelected(1 to 1)
j = 1
for i = 1 to 10
if vSelect(i) then
Redim Preserve(vSelected(1 to j))
vSelected(j) = vSelect(i)
j = j + 1
Dim sSum as String, m as long, n as long, k as long, s as String
Dim r as Range, r1 as range, vTot as Variant, v as Variant
Dim sh as worksheet
sSum = "All Scenarios" ' sheet name of the summary sheet
for i = 1 to 4
s = "Rng" & i
set r1 = worksheets(sSum).Names(s).ReferstoRange
' zero values in sheet "All Scenarios" for this range name
r1.value = 0
' now load all those zero values in to the vTot array
vTot = r1.value
' now loop through the selected sheets
for k = 1 to Ubound(vSelected)
set sh = worksheets(vSelected(k))
set r = sh.Names(s).ReferstoRange
' load values into array v
v = r.Value
' now loop through the array
for m = 1 to Ubound(v,1)
for n = 1 to Ubound(v,2)
vTot(m,n) = vTot(m,n) + v(m,n)
Next n ' looping through columns
Next m ' looping through rows
Next k 'Next selected sheet
' now write vtot array back to the summary range
r1.value = vTot
Next i ' next of the 4 range names
So I would approach it something like the above.