Alternate syntax...

[Guest]

What are some alternate ways of writing this...

maxDim =
Workbooks("AF-Scenario.xls").Worksheets("Settings").Range("E2").Value + _
Workbooks("AF-Scenario.xls").Worksheets("Settings").Range("E3").Value
+ _
Workbooks("AF-Scenario.xls").Worksheets("Settings").Range("E4").Value
+ _
Workbooks("AF-Scenario.xls").Worksheets("Settings").Range("E5").Value
+ _
Workbooks("AF-Scenario.xls").Worksheets("Settings").Range("E6").Value
+ _
Workbooks("AF-Scenario.xls").Worksheets("Settings").Range("E7").Value
+ _
Workbooks("AF-Scenario.xls").Worksheets("Settings").Range("I2").Value
+ _
Workbooks("AF-Scenario.xls").Worksheets("Settings").Range("I3").Value
+ _
Workbooks("AF-Scenario.xls").Worksheets("Settings").Range("I4").Value
+ _
Workbooks("AF-Scenario.xls").Worksheets("Settings").Range("I5").Value
+ _
Workbooks("AF-Scenario.xls").Worksheets("Settings").Range("I6").Value
+ _
Workbooks("AF-Scenario.xls").Worksheets("Settings").Range("I7").Value
ReDim myArray(maxDim) As String

I tried to use the Union method but wasn't successful. But I am curious
about the alternate ways to write this. Less coding is prefered. But I'm
still interested in any way to write/accomplish this same thing.

Is there some sort of ... "x = Range("E2:E7,I2:I7").sum" VB statement that
would work?

 

[Bob Umlas]

maxDim =
Application.Sum(Workbooks("AF-Scenario.xls").Worksheets("Settings").Range("E
2:E7,I2:I7"))

 

[Trevor Shuttleworth]

John

maybe somethink like:

With Workbooks("AF-Scenario.xls").Worksheets("Settings")
maxdim = Application.WorksheetFunction.Sum(Range("E2:E7,I2:I7"))
End With

Regards

Trevor

 

[Guest]

I knew there had to be a way to use sum... I need to look closer at what's
available as application.____ and build on that. Thanks Bob

maxDim =
Application.Sum(Workbooks("AF-Scenario.xls").Worksheets("Settings").Range("E
2:E7,I2:I7"))

 

[Bob Phillips]

Either

With Workbooks("AF-Scenario.xls").Worksheets("Settings")
maxDim = .Range("E2").Value + _
.Range("E3").Value + _
.Range("E4").Value + _
.Range("E5").Value + _
.Range("E6").Value + _
.Range("E7").Value + _
.Range("I2").Value + _
.Range("I3").Value + _
.Range("I4").Value + _
.Range("I5").Value + _
.Range("I6").Value + _
.Range("I7").Value
End WIth
ReDim myArray(maxDim) As String

Or

With Workbooks("AF-Scenario.xls").Worksheets("Settings")
maxDim = Appliction.SUM(.Range("E2:E7,I2:I7"))
End WIth
ReDim myArray(maxDim) As String


--

HTH

RP
(remove nothere from the email address if mailing direct)