how to increment numbers in a range and ignore blanks

[dmills335]

hello,

i am trying to increment the cells that hold a numerical value within a range whilst ignoring blank cells.
i am using the code below, but it has no affect:

Set cellrange = Range("M8:M44").Cells.SpecialCells(xlCellTypeConstants)
For Each cell In cellrange
cell.Value = cell.Value + 1
Next

any help is greatly appreciated.

 

[mumps]

Maybe:

Set cellrange = Range("M8:M44").Cells.SpecialCells(xlCellTypeConstants)
For Each cell In cellrange
    If cell <> "" Then
        cell.Value = cell.Value + 1
    End If
Next cell

 

[Fluff]

@dmills335
Your code works for me.
What do you mean by "It has no affect"
Do the cells contain formulae?
Are your "numbers" proper numbers or text?

 

[dmills335]

hi fluff,
there was no change to the cell values, although when i removed the .Cells.SpecialCells(xlCellTypeConstants) all the cells incremented.
the cells are formatted as numbers.

 

[dmills335]

thanks mumps,

i thought the same as you and used almost identical code.
it does work after a fashion.
i failed to mention that i need this to work over 3 separate worksheets.
when i used this method it worked for sheet 1, but not for sheets 2 or 3.

essentially i used this method on sheet 1, then stepped into sheet 2 and used this method on a different range and then stepped into sheet 3 for a different range again.
however, it didn't work for sheets 2 and 3.

 

[Fluff]

there was no change to the cell values, although when i removed the .Cells.SpecialCells(xlCellTypeConstants) all the cells incremented.

That sounds like the numbers are the result of formulae, rather than hard values.

 

[dmills335]

just normal numbers in cells that are formatted as numbers.
i want them to increment when the date changes.

 

[mumps]

How did it not work for sheets 2 and 3? Did it work on some cells and not others or not at all? Did it generate any error message? Is the date changed manually or is it the result of a formula? In which cell is the date?

 

[dmills335]

i have no idea why it didn't work on the other 2 sheets.
no error messages.
the date is currently changed manually, but the aim is to use the same method to increments that as well.

 

[mumps]

I think that it would be easier to help and test possible solutions if I could work with your actual file which includes any macros you are currently using. Perhaps you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Include a detailed explanation of what you would like to do using a few examples from your data and referring to specific cells, rows, columns and worksheets. If the workbook contains confidential information, you could replace it with generic data.