VBA to increase numbers by 1

[Yamasaki450]

Hi everyone...

I would like to increase all numbers in range B2:N3 by one. Like shown on screenshot.
Is anyone able to write VBA code for this? I already tried this with formula bellow but it only works on positive numbers and not on negatives. My range is of data is quite long in some cases and i need to drag formula across the whole range every time... so VBA code would be much easier...

Or maybe there is another way to do this?

Formula i tried

=TEXTJOIN("-",,FILTERXML("<k><m>"&SUBSTITUTE(A4,"-","</m><m>")&"</m></k>","//m")+1)

 

[Joe4]

Have you ever tried the Copy/Paste Special/Add feature, which makes this extremely easy?

Simply put a 1 in any blank cell outside of the range you want to add it to.
Then, select that cell and click "Copy".
Then, select the entire range you wish to update, and right-click and go to Paste Special -> Add and press OK.

That will add that number to every cell in your range at once!
You could also probably automate these steps using VBA, though if your range can change, you would either want to select that from the start, or prompt the user for it.

 

[Fluff]

How about

Fluff.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M	N
1
2	7	-32	-22	-29	35	-65	-48	65	32	-172	-41	262	103
3	8	-19	-21	-25	25	-43	-41	59	24	-147	-38	233	100
4
5
6
7
8
9
10	8	-33	-23	-30	36	-66	-49	66	33	-173	-42	263	104
11	9	-20	-22	-26	26	-44	-42	60	25	-148	-39	234	101
Main

Cell Formulas
Range	Formula
B10:N11	B10	=B2:N3+SIGN(B2:N3)
Dynamic array formulas.

 

[Joe4]

Ah, I missed the fact that it appears that they want to ADD one to positive numbers, and SUBTRACT one from negative numbers.
(The title is a little misleading, but the image clarifies that).

So my solution would not do that, but Fluff's should.

 

[Scott Huish]

Without a formula or VBA, you could just put 1 in a cell. Copy it. Select your Entire Range, right click, Click on Paste Special Add.

@Fluff, does that work with Excel 2021?

 

[Fluff]

@Fluff, does that work with Excel 2021?

Should do.

 

[Joe4]

Without a formula or VBA, you could just put 1 in a cell. Copy it. Select your Entire Range, right click, Click on Paste Special Add.

@Fluff, does that work with Excel 2021?

Scott, you are making the exact same mistake I did. I originally recommended the same thing, but upon further inspection, saw that was NOT what they are after.
Have a look at my two posts above, and look closely at the original image, which includes desired output.

For example, adding 1 to -32 results in -31, when it appears that they actually want -33.

 

[Yamasaki450]

How about
=B2:N3+SIGN(B2:N3)

This does exactly what i need. Thanks a lot. Again

Have you ever tried the Copy/Paste Special/Add feature, which makes this extremely easy?

Simply put a 1 in any blank cell outside of the range you want to add it to.
Then, select that cell and click "Copy".
Then, select the entire range you wish to update, and right-click and go to Paste Special -> Add and press OK.

That will add that number to every cell in your range at once!
You could also probably automate these steps using VBA, though if your range can change, you would either want to select that from the start, or prompt the user for it.

Without a formula or VBA, you could just put 1 in a cell. Copy it. Select your Entire Range, right click, Click on Paste Special Add.

@Fluff, does that work with Excel 2021?

This will come useful too in some cases later on...

Thanks for your help guys. Have a nice day...

 

[Fluff]

Glad we could help & thanks for the feedback.