Macro to Increment a Cell Value by One

[Puppies72]

Hi all,

I need a macro (which I can assign to a button) to increment the values in a range of cells from AD4:AD204 by 1 but only if the cell is not empty.

Basically I am updating a sheet and some of the cells in that range will be empty until I add data so when I click on my "Update Sheet" button it will increment the values by one for cells with data in and then when I add new data and update those cells will begin their incrementation.

Hope that makes sense

thanks in advance!!

 

[alvinwlh]

Hi You can try

Sub Macro5()
    On Error Resume Next
    Set C = Range("AD4:AD204").Cells.SpecialCells(xlCellTypeConstants)
    For Each cc In C
        cc.Value = cc.Value + 1
    Next
End Sub

 

[Puppies72]

Fantastic! Works a charm...

Is there anyway to further modify that to update 10 sheets at once with a single click?

 

[alvinwlh]

Hi,

How many sheets does this workbook has?

or what are the name of the sheets?

Alvin

 

[Puppies72]

I only want it to update certain sheets, those sheet names are reps:

Bart
Craig
Dan
Iain
Jason
Julian
Marc
Max
Neil
Pete
Ricky
Steve

many thanks

 

[Jon von der Heyden]

perhaps;

[COLOR="Blue"]Public[/COLOR] [COLOR="Blue"]Sub[/COLOR] phoo1()
    [COLOR="Blue"]Dim[/COLOR] strRange [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]String[/COLOR]
    [COLOR="Blue"]Dim[/COLOR] wks [COLOR="Blue"]As[/COLOR] Worksheet
    
    [COLOR="Blue"]For[/COLOR] [COLOR="Blue"]Each[/COLOR] wks [COLOR="Blue"]In[/COLOR] ThisWorkbook.Worksheets
        strRange = wks.Range("AD4:AD204").Address(external:=[COLOR="Blue"]True[/COLOR])
        Range(strRange).Value = _
        Evaluate("IF(ROW(),IF(ISNUMBER(" & strRange & ")," & strRange & "+1,IF(" & strRange & "="""",""""," & strRange & ")))")
    [COLOR="Blue"]Next[/COLOR] wks
[COLOR="Blue"]End[/COLOR] [COLOR="Blue"]Sub[/COLOR]

 

[Jon von der Heyden]

I only want it to update certain sheets, those sheet names are reps:

Bart
Craig
Dan
Iain
Jason
Julian
Marc
Max
Neil
Pete
Ricky
Steve

many thanks

Try;

[COLOR="Blue"]Public[/COLOR] [COLOR="Blue"]Sub[/COLOR] phoo1()
    [COLOR="Blue"]Dim[/COLOR] strRange [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]String[/COLOR]
    [COLOR="Blue"]Dim[/COLOR] wks [COLOR="Blue"]As[/COLOR] Worksheet
    
    [COLOR="Blue"]For[/COLOR] [COLOR="Blue"]Each[/COLOR] wks [COLOR="Blue"]In[/COLOR] Sheets([COLOR="Blue"]Array[/COLOR]("Bart", "Craig", "Dan", "Iain", "Jason", "Julian", "Marc", "Max", "Neil", "Pete", "Ricky", "Steve"))
        strRange = wks.Range("AD4:AD204").Address(external:=[COLOR="Blue"]True[/COLOR])
        Range(strRange).Value = _
        Evaluate("IF(ROW(),IF(ISNUMBER(" & strRange & ")," & strRange & "+1,IF(" & strRange & "="""",""""," & strRange & ")))")
    [COLOR="Blue"]Next[/COLOR] wks
[COLOR="Blue"]End[/COLOR] [COLOR="Blue"]Sub[/COLOR]

 

[alvinwlh]

Hi,

I suggest you open a new sheet with called Sheet1
And put all the name under column A:A
Example A1 = Bart, A2 = Craig
So the code will be:

Sub Macro5()
    Set s = Sheets
    For Each ss In s
        Set c = Worksheets("Sheet1").Range("a:a").Find(ss.Name, LookIn:=xlValues, lookat:=xlWhole)
        If Not c Is Nothing Then
            On Error Resume Next
            Set c = Range("AD4:AD204").Cells.SpecialCells(xlCellTypeConstants)
            For Each cc In c
                cc.Value = cc.Value + 1
            Next
        End If
    Next
End Sub

 

[alvinwlh]

Ya You can also follows Jon's method.

 

[Puppies72]

Hi guys,

Tried Jon's method and I'm getting an error saying that the macro is not available in current workbook or all macros may be disabled (which is not the case) though I may have done something wrong so don't assume it is your code!

have to head off to a meeting now but will try Alvin's method after lunch.

many thanks