need macro to edit formula...

[Mo2]

this is difficult to explain, but here goes nothing...

here's my sample formula, in Cell G25:
=IF(D25=0,0,IF(D45=0,0,B45))

This is what i want the macro to do:
Step1: read the number in cell A1. (lets say i type "2" in there)

Step2: Add "2" to every cell reference in G25's formula, except the
reference that refers to the current cell's row (in this case, row 25).

I.E. this would change G25's formula from: =IF(D25=0,0,IF(D47=0,0,B47))
to:
=IF(D25=0,0,IF(D47=0,0,B47))

STEP3: copy the new formula down for 20 rows.

I need this same thing to happen, if i type a number in A2
(except this time, it would be looking at the formula in I25
and following the same steps.
I25's formula is : =IF(D25=0,0,IF(D44=0,0,B44))


can anybody help me please?
if this can be done through some sort of pop up (which startup as soon as i
open the worksheet), instead of typing a number in a cell, even better. but
either way will work for me

thanks in advance

 

[Vergel Adriano]

If I understand it correctly, I think this should work (or at least give you
some idea to do what you're trying to accomplish). Try putting this formula
in G25:

=IF(D25=0,0,IF(INDIRECT("D" & 45+$A$1)=0,0,INDIRECT("B" & 45+$A$1)))

then copy down as needed. The row number of the cell references will always
be 45 plus whatever number is in A1. If you need the row number to increment
as you copy down, try it this way:

=IF(D25=0,0,IF(INDIRECT("D" & ROW()+20+$A$1)=0,0,INDIRECT("B" &
ROW()+20+$A$1)))

then copy down as needed.

 

[Mo2]

nice! thank you it works

i'll let you know if i have any more problems

thanks again

 

[Mo2]

problem!

when the formula was: =IF(D25=0,0,IF(D45=0,0,B45))
and when i copied it down,
it became =IF(D26=0,0,IF(D46=0,0,B46))
thats how i wanted it..

but with this formula:
=IF(D25=0,0,IF(INDIRECT("D" & 45+$A$1)=0,0,INDIRECT("B" & 45+$A$1)))

copied down, only the "D25" would will update to "D26" and so on.
but the "45" will remain a 45 when copy the formula down..
what to do?

i have hundreds of cells.. it will take me forever to change the 45 to 46,
47, 48, etc (for every new row i copy it down to)

is there another way?

 

[Vergel Adriano]

Hi,

Try the second formula that I gave.

=IF(D25=0,0,IF(INDIRECT("D" & ROW()+20+$A$1)=0,0,INDIRECT("B" &
ROW()+20+$A$1)))

 

[Mo2]

hey.. umm...something's not right..
its still the same. As i copy the formula down,

the "D25" changes to "D26", "D26", etc, as it should..
but the "+20" stays as "+20" -- when it should change to "+21", "+22", etc
, as its copied down..

whats wrong?

 

[Mo2]

after fiddling around with it for a while, i think i answered my own question
this does the trick:
=IF(D25=0,0,IF(INDIRECT("D"&ROW(D45)+$A$1)=0,0,INDIRECT("B"&ROW(B45)+$A$1)))

thanks a lot.
I'll post back if i need more assistance

 

[Vergel Adriano]

Hi,

When you put this

=ROW(D45)

in row 20, it will give you 45 and when you copy it down, you will see the
cell reference D45 increment. This formula:

=ROW() + 20

will give you the same result. But, you won't see any change in the formula
as you copy it down because ROW() will give you the current row number so it
increments internally when Excel evaluates the formula...