Increment values

[siddharth_kale]

Hi,

I would like to use the below function by specifying a range of cells. Can someone help here:

Sub Up_One()
Range("C4").Value = Range("C4").Value + 1
Range("C5").Value = Range("C5").Value + 1
Range("C6").Value = Range("C6").Value + 1
End Sub

Instead of mentioning each cell I would like to directly mention C4:C6.

Any help is appreciated.

Regards,
Siddharth

 

[Andrew Poulsom]

You can use a loop:

Sub Up_One()
    Dim Cell As Range
    For Each Cell In Range("C4:C6")
        Cell.Value = Cell.Value + 1
    Next Cell
End Sub

 

[Ruddles]

Andrew, is there any way of using PasteSpecial to do the adding?

I'm just thinking out loud...

 

[Andrew Poulsom]

Andrew, is there any way of using PasteSpecial to do the adding?

I'm just thinking out loud...

Yes, you could put 1 in a cell, copy it and paste special add.

Sub Up_One()
    With Range("A1")
        .Value = 1
        .Copy
        Range("C4:C6").PasteSpecial Paste:=xlPasteValues, Operation:=xlAdd
        .ClearContents
    End With
End Sub

 

[Ruddles]

Thanks.

 

[siddharth_kale]

Great thanks Andrew.

One more thing: could the marcro be scheduled to run on 15th of every month? something like this: http://www.ozgrid.com/Excel/run-macro-on-time.htm