Do Until

[Guest]

I have a Workbook with 13 worksheets in it that I use as a template week to
week. What I want to do is create a macro (Do Until) to look in each cell
from column D through column H to see if there is a number greater than zero
and if so change to zero and then go to the next worksheet and do the same
thing. I start out each week with a clean workbook (no data inputed). I
wrote one using the macro record, however there are times that I have to
delete or insert rows so mine will not work then. Any help would be
appreciated.

 

[Guest]

Sub cleanout()
For Each ws In Sheets
ws.Activate
Set rr = Intersect(ActiveSheet.UsedRange, Range("D:H"))
If Not rr Is Nothing Then
For Each r In rr
If r.Value > 0 Then
r.Value = 0
End If
Next
End If
Next
End Sub

 

[JE McGimpsey]

One way:

Public Sub ClearNumbers()
Dim ws As Worksheet
Dim rNumbers As Range
For Each ws In ActiveWorkbook.Worksheets
On Error Resume Next
Set rNumbers = ws.Range("D:H").SpecialCells( _
xlCellTypeConstants, xlNumbers)
On Error GoTo 0
If Not rNumbers Is Nothing Then
rNumbers.Value = 0
Set rNumbers = Nothing
End If
Next ws
End Sub

 

[Guest]

Works great, however, I miss counted. I just took this over from someone else
so I failed to count the ws's that I don't want to use.

There are actually 30 worksheets of which I only want to run the macro in
the following ones:

C2k - 1x
C2K-EVDO Rev O
C2K-EVDO Rev A
Product Stress
Product SysTest
DO CT
1x Compliance
CSW
Bluetooth
WLAN
Broadcase
Multimedia
IMS & IP

Sorry for the confusion.

Thanks
Frank

 

[Guest]

In place of the:
For each
insert these three lines:

s = Array("C2k - 1x", "C2K-EVDO Rev O", "C2K-EVDO Rev A", "Product Stress",
"Product SysTest", "DO CT", "1x Compliance", "CSW", "Bluetooth", "WLAN",
"Broadcase", "Multimedia", "IMS & IP")
For i = 0 To 12
ws = Sheets(s(i))