Array formula not working

[dignityy]

Here is the code:

Sub ClearDownloadAndPeriodTabs()
'
' ClearDownloadAndPeriodTabs Macro
' Macro recorded 10/17/2006

Application.ScreenUpdating = False


Sheets("FRx Download").Rows("2:3000").ClearContents
Sheets(Array("P1", "P2", "P3", "P4", "P5", "P6", "P7", "P8", "P9", "P10", "P11", "P12")).Select
Columns("A:F").ClearContents
Sheets("Clear").Select
Range("G16").Select
Application.ScreenUpdating = True

End Sub




Only the FRx Download and the P1 tabs are cleared? What am I missing?
Thanks

 

[Fenrirshowl]

Is this better?

Sub ClearDownloadAndPeriodTabs()
Application.ScreenUpdating = False
Sheets("FRx Download").Rows("2:3000").ClearContents
For x=1 to 12
Sheets("P"&x).Columns("A:F").ClearContents
next x
Sheets("Clear").Select
Range("G16").Select
Application.ScreenUpdating = True
End Sub

 

[dignityy]

I get a compile error when using that code that says x is not definted?
Doesn't "For x = 1 to 12" define it?

 

[Fenrirshowl]

You can define it earlier as an integer or long, long appears to be the preferred habit (from reading other posts) as issues occur when you hit big numbers.

Suggest:

Dim x as Long

 

[SkipVought]

FYI,

You have Option Explicit which forces you to define variables explicitly, which is a good practice.

For counting variables (whole numbers) that you know will NEVER exceed 32,767, use Integer. The number of sheets or the number of columns in a sheet might apply here.

However ROWS can exceed that limit, so a ROW counter ought to always be defined as Long.

Skip,

_[red][/red]

 

[Fenrirshowl]

Like I said "issues"!

Thanks Skip - its nice to know you keep a watchful eye over us.

 

[dignityy]

Thanks that fixed the issue! Just for learning sake do you guys know what was wrong with my original Array formula?
Thankyou

 

[Fenrirshowl]

Using Sheets( ) you need to reference either a sheet position or a sheet name. You are using an array (which is not recognised as either) and therefore an error is triggered.

You could have used the array of sheet names and referred to each sheet by the array item in turn (similar to the For...Next method).

 

[GlennUK]

Hi dignityy,

group editing of sheets doesn't work through macros, only when doing it by hand in native Excel.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.