Excel/follow up


hi Tom,
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.  

Sub abc()
Dim r As Range, v As Variant
Set r = Range("A1:J10")
v = r.Value
Process r
Process v
End Sub

Public Function Process(vv As Variant)
 tot = 0
 For i = 1 To 10
   For j = 1 To 10
      tot = tot + vv(i, j)
   Next j
 Next i
 MsgBox TypeName(vv) & ": " & tot
End Function

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
 end if
Next i

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.

Tom Ogilvy  
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here


All Answers

Answers by Expert:

Ask Experts


Tom Ogilvy


Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]


Extensive experience.

Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2016 About.com. All rights reserved.