vba sheets(array(

[EOAEvan]

The following code does not work on the sheets in the array. Instead it only works on the active sheet. Why?

Sub ClearContents()


frow = Array(9, 15, 21, 26, 35, 37, 42, 45, 50, 53, 58, 61, 64, 69, 71, 76, 80, 83, 86, 91, 94, 97, 102, 105, 108, 113, 127, 134, 138, 144, 147, 153, 156, 160, 164, 168, 173, 177, 187)
trow = Array(13, 19, 24, 32, 35, 39, 43, 46, 51, 54, 59, 62, 67, 69, 74, 78, 81, 84, 89, 92, 95, 100, 103, 106, 111, 123, 132, 136, 142, 145, 148, 154, 158, 161, 166, 170, 175, 182, 188)

For i = LBound(frow) To UBound(frow)
    With Sheets(Array("ROF Mon", "ROF Tue", "ROF Wed", "Act Thu", "Act Fri"))
    Range("D" & frow(i) & ":E" & trow(i)).ClearContents
    End With
Next i

End Sub

 

[WaterGypsy]

I think (only think) it might because WITH can only work on a single object and as such your WITH array is not understood by vba ... ofr some reason it isn't crashing but it is ignoring the statement ....

of course I am willing to be corrected

Kevin

 

[GlennUK]

Maybe:

Sub ClearContents()


frow = Array(9, 15, 21, 26, 35, 37, 42, 45, 50, 53, 58, 61, 64, 69, 71, 76, 80, 83, 86, 91, 94, 97, 102, 105, 108, 113, 127, 134, 138, 144, 147, 153, 156, 160, 164, 168, 173, 177, 187)
trow = Array(13, 19, 24, 32, 35, 39, 43, 46, 51, 54, 59, 62, 67, 69, 74, 78, 81, 84, 89, 92, 95, 100, 103, 106, 111, 123, 132, 136, 142, 145, 148, 154, 158, 161, 166, 170, 175, 182, 188)

Sheets(Array("ROF Mon", "ROF Tue", "ROF Wed", "Act Thu", "Act Fri")).Select

For i = LBound(frow) To UBound(frow)
    Range("D" & frow(i) & ":E" & trow(i)).ClearContents
Next i
Sheets("ROF Mon").Select

End Sub

 

[EOAEvan]

Interesting... Any suggestions on how to make it work? Other than repeating the code for each sheet individually?

 

[EOAEvan]

Maybe:

Sub ClearContents()


frow = Array(9, 15, 21, 26, 35, 37, 42, 45, 50, 53, 58, 61, 64, 69, 71, 76, 80, 83, 86, 91, 94, 97, 102, 105, 108, 113, 127, 134, 138, 144, 147, 153, 156, 160, 164, 168, 173, 177, 187)
trow = Array(13, 19, 24, 32, 35, 39, 43, 46, 51, 54, 59, 62, 67, 69, 74, 78, 81, 84, 89, 92, 95, 100, 103, 106, 111, 123, 132, 136, 142, 145, 148, 154, 158, 161, 166, 170, 175, 182, 188)

Sheets(Array("ROF Mon", "ROF Tue", "ROF Wed", "Act Thu", "Act Fri")).Select

For i = LBound(frow) To UBound(frow)
    Range("D" & frow(i) & ":E" & trow(i)).ClearContents
Next i
Sheets("ROF Mon").Select

End Sub

This is still only performing the code on the active sheet.

 

[GlennUK]

Change

 Range("D" & frow(i) & ":E" & trow(i)).ClearContents

to

 Range("D" & frow(i) & ":E" & trow(i)).Select
Selection.ClearContents

 

[WaterGypsy]

You coudl try

For each ws in Worksheets
if ws.name = "ROF Mon" or ws.name = "ROF Tue" or ws.name = etc etc then
For i ........
Next i
next ws

 

[EOAEvan]

Glenn... That worked. Thanks for the help!!

 

[GlennUK]

My pleasure!