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

On 13 Apr., 14:47, Colin Hayes wrote:
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

Hi Colin

I assume that you only have the sheets mentioned in your workbook.

This code will do what you want.

Sub SubtractInRange()
Application.ScreenUpdating = False
TargetRange = "A3:N35"
For sh = 1 To Sheets.Count
Sheets(sh).Select
For Each c In Range(TargetRange)
If IsNumeric(c.Value) = True Then
c.Value = c.Value - 1
If c.Value < 0 Then c.Value = ""
End If
Next
Next
Application.ScreenUpdating = True
End Sub

Regards,
Per

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

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.

BTW 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

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



"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.

BTW 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

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

First off, you never mentioned dates in your original question. As a matter
of fact, you specifically requested blanking out the cell if the "number"
became negative... a date can never be negative. Hence, the reason I and Per
gave you the code we did.

Okay, now, as to your question given your latest posting... the code below
will blank out the cell if the date it contains is the first of the month;
however, I do not understand what you mean when you say the "last day of the
month is missing altogether". If you are blanking out a cell when one is
subtracted from the first of the month, then there will be no date (only an
empty cell) to subtract one from in order to get the last day of the month;
and if you start with the end of the month, then my code (and I'm pretty
sure Per's code) will properly decrement it by one. So, can you clarify what
you meant, or what you were looking to have happen, when you said that?

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 IsDate(CL.Value) Then
If Day(CL.Value) = 1 Then
CL = ""
Else
CL.Value = CL.Value - 1
End If
End If
Next
Next
Application.ScreenUpdating = True
End Sub

Rick



"Colin Hayes" wrote in message
...
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