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