In article , Rick Rothstein (MVP
- VB) writes
Just out of curiosity, what anomalous results are you getting?

If you are running my code, it will leave you on the same sheet you call the
macro from. However, if you to explicitly go to sheet #1, add this....

Worksheets(1).Select

at the end of the macro (probably before setting ScreenUpdating to True
would make the most sense).

Rick

Hi Rick

I'm applying the code to dates in each of the sheets. I had to change
the
IsNumeric element to IsDate , of course.

It works OK but the first day of the month , which should become 0 and
therefore be blanked out is actually reading 31. The last day of the
month is missing altogether. This is repeated over all 12 sheets.

I can live with this and amend manually , although it would be nice if
it did the whole job. I know dates can get complicated.

Best Wishes





"Colin Hayes" wrote in message
...


Hi guys

OK thanks - that's done the trick.

It does give a couple of anomalous results , I find , but I can work them
out.

Thanks again.

B

TW Per , could you add a line to the end of the code you gave to select
the first sheet in the wb on completion of the macro?

^_^





In article , Rick Rothstein
(MVP -
VB) writes
Give this a try...

Sub DecreaseByOne()
Dim WS As Worksheet
Dim CL As Range
Application.ScreenUpdating = False
For Each WS In Worksheets
For Each CL In WS.Range("A3:N35")
If IsNumeric(CL.Value) Then CL.Value = CL.Value - 1
If CL.Value < 0 Then CL = ""
Next
Next
Application.ScreenUpdating = True
End Sub

Rick


"Colin Hayes" wrote in message
...

Hi

I need some help constructing a macro.

I have a workbook with 12 sheets , A - L.

In each sheet , range A3 - N35 contains a series of numbers , with some
cells being blank.

I need a macro which will go through each sheet , reducing each number
it
finds by 1.

Blank cells should be left blank. Where a cell becomes negative after
subtracting 1 from it , this should be made blank too.

Can someone help?

Grateful for any assistance.



Best Wishes