how to increment numbers in a range and ignore blanks

dmills335

New Member
Joined
Jun 14, 2018
Messages
21
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.
 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,095
Maybe:
Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,727
Office Version
  1. 365
Platform
  1. Windows
@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

New Member
Joined
Jun 14, 2018
Messages
21
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

New Member
Joined
Jun 14, 2018
Messages
21

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,727
Office Version
  1. 365
Platform
  1. Windows
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.
 
Last edited:

dmills335

New Member
Joined
Jun 14, 2018
Messages
21

ADVERTISEMENT

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

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,095
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?
 
Last edited:

dmills335

New Member
Joined
Jun 14, 2018
Messages
21
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

Well-known Member
Joined
Apr 11, 2012
Messages
10,095
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,168
Messages
5,594,632
Members
413,919
Latest member
ZaxAlchemist

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top