Why won't this work?

[Steph]

Hello. I'm sure this a pretty dumb question, but any ideas why this
won't work??

Set clrarray = Sheets(Array("Forecast", "LMU", "Kit", "SMLC", "WLG",
"SMLC Cab", "Serv Cab", "Ntwk Kit", _
"TDAX", "EMS", "SCOUT", "Dir Coup"))

ActiveWorkbook.Worksheets(clrarray).Range("A5:EC500").ClearContents

Thanks!

 

[Bob Phillips]

TRy

For Each sh In ActiveWorkbook.Worksheets(clrarray)
sh.Range("A5:EC500").ClearContents
Next sh


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Frank Kabel]

Hi
try something like the following:
Sub foo()
Dim sh_array As Variant
Dim wks As Worksheet
Dim i
sh_array = Array("sheet1", "sheet2")
For i = 0 To UBound(sh_array)
Set wks = Worksheets(CStr(sh_array(i)))
wks.range("A5:EC500").clearcontents
'MsgBox wks.Name
Next
End Sub

 

[Tushar Mehta]

There are two problems with the code.

First, you are setting ClrArray to a collection of sheets. Then, you
are trying to use this collection of *objects* as indices to the
Worksheets collection. That cannot work because the Worksheets
collection can be indexed only by integers or strings (and not
objects).

You should use

'Set ClrArray = ActiveWorkbook.Sheets(Array("Forecast", _
"LMU", "Kit", "SMLC", "WLG", _
"SMLC Cab", "Serv Cab", "Ntwk Kit", _
"TDAX", "EMS", "SCOUT", "Dir Coup"))
ClrArray = Array("Sheet1", "Sheet2", "Sheet3")
ActiveWorkbook.Sheets(ClrArray).Select

In the above example, your statment has been commented out. My test
used just three worksheets. Note that in my case, clrarray contains
*string* values not worksheet objects.

However, that still leaves the 2nd problem. XL's handling of 3D ranges
is something I would describe as a layer slapped on over the existing
object model. Since it doesn't quite fit in with the OM, you have to
rely on the 'selection' to make things happen. The code below works.
Note that, once again, the correct clrarray statement for you exists
only as a comment.

Option Explicit

Option Explicit

Sub testIt()
Dim ClrArray
'ClrArray = Array("Forecast", _
"LMU", "Kit", "SMLC", "WLG", _
"SMLC Cab", "Serv Cab", "Ntwk Kit", _
"TDAX", "EMS", "SCOUT", "Dir Coup")
ClrArray = Array("Sheet1", "Sheet2", "Sheet3")
ActiveWorkbook.Sheets(ClrArray).Select
Sheets(ClrArray(LBound(ClrArray))).Activate
Range("A5:EC500").Select
Selection.ClearContents
End Sub



--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions