Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Do Until
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Do Until
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 -- Gary''s Student - gsnu200728 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Do Until
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 In article , Beep Beep wrote: 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Do Until
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 "Gary''s Student" wrote: 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 -- Gary''s Student - gsnu200728 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Do Until
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)) -- Gary''s Student - gsnu200728 "Beep Beep" wrote: 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 "Gary''s Student" wrote: 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 -- Gary''s Student - gsnu200728 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|