How to Increase Part of a Cell Value Using a Macro

[zacksmyth]

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!

 

[Marcelo Branco]

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]

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]

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.