How to Increase Part of a Cell Value Using a Macro

zacksmyth

New Member
Joined
Feb 12, 2013
Messages
2
Hello,
I'm am trying to increase part of the value of a cell using a macro. The formula is very complicated, so it takes a long time to edit manually. Here is what I would like it to do:

Input cell value:
=IF(RAW!A2<=PERM!A$5,7,IF(RAW!A2<=PERM!B$5,6,IF(RAW!A2<=PERM!C$5,5,IF(RAW!A2<=PERM!D$5,4,IF(RAW!A2<=0.9,3)))))

Output cell value:
=IF(RAW!A2<=PERM!A$6,7,IF(RAW!A2<=PERM!B$6,6,IF(RAW!A2<=PERM!C$6,5,IF(RAW!A2<=PERM!D$6,4,IF(RAW!A2<=0.9,3)))))

Is there a macro (or some other method) that would allow me to automatically increase those values in the cell by 1 each time?

I've been doing it manually and wish excel came with a cyanide pill :(

Thank you so much!
 

Some videos you may like

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,371
Hi and welcome to Mr Excel Forum

Maybe this (observe the $ )

Input cell
=IF(RAW!A$2<=PERM!$A5,7,IF(RAW!A$2<=PERM!$B5,6,IF(RAW!A$2<=PERM!$C5,5,IF(RAW!A$2<=PERM!$D5,4,IF(RAW!A$2<=0.9,3)))))

Then copy down (drag manually) and in the cell below you get
=IF(RAW!A$2<=PERM!$A6,7,IF(RAW!A$2<=PERM!$B6,6,IF(RAW!A$2<=PERM!$C6,5,IF(RAW!A$2<=PERM!$D6,4,IF(RAW!A$2<=0.9,3)))))

Is this what you need?

M.
 

zacksmyth

New Member
Joined
Feb 12, 2013
Messages
2
To clarify, I need the formula to remain the same in every row that I copy it to on the worksheet (ergo the $).
The problem is that I need it to increase by one on each worksheet (e.g. the first worksheet to have the formula have the value 1, the second worksheet to have the formula have the value 2, etc.). Though perhaps it might be easier to have one sheet where I simply drag it down without the $, and then have each subsequent worksheet copy the formula from the following row.

Hmmm. I think you've helped me get closer to cracking this one...


Hi and welcome to Mr Excel Forum

Maybe this (observe the $ )

Input cell
=IF(RAW!A$2<=PERM!$A5,7,IF(RAW!A$2<=PERM!$B5,6,IF(RAW!A$2<=PERM!$C5,5,IF(RAW!A$2<=PERM!$D5,4,IF(RAW!A$2<=0.9,3)))))

Then copy down (drag manually) and in the cell below you get
=IF(RAW!A$2<=PERM!$A6,7,IF(RAW!A$2<=PERM!$B6,6,IF(RAW!A$2<=PERM!$C6,5,IF(RAW!A$2<=PERM!$D6,4,IF(RAW!A$2<=0.9,3)))))

Is this what you need?

M.
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,371
To clarify, I need the formula to remain the same in every row that I copy it to on the worksheet (ergo the $).
The problem is that I need it to increase by one on each worksheet (e.g. the first worksheet to have the formula have the value 1, the second worksheet to have the formula have the value 2, etc.). Though perhaps it might be easier to have one sheet where I simply drag it down without the $, and then have each subsequent worksheet copy the formula from the following row.
Sorry, i'm not following you. I didn't know that you want to copy the formula to more than one worksheet.

Hmmm. I think you've helped me get closer to cracking this one...
I hope I really helped. Good luck!

M.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,122
Messages
5,466,814
Members
406,500
Latest member
Tknotmaxx

This Week's Hot Topics

Top