macro to alter a formula

[Crispy1974]

Hello, I've used excel in a very basic sense for years but am getting frustrated with time consuming tasks that I'm sure could be automated. I have a problem I'm trying to create a macro for but the macro screws up the cell references. Is there any way for me to automate the following:

I have a large dataset and will recreate the issue with a small made up example.

A
1) 200
2) 250
3) 285
4) 178
5) =if(A1/A4*100>110,"e",if(A1/A4*100<90,"i","-"))
6) =if(A2/A3*100>110,"e",if(A1/A4*100<90,"i","-"))

A is a dataset of a soil analysis which lists the values of soil elements from different sites. The criterion is pretty random and required me to go into the data to see which value should divide by another. ie it was not a case of making the formula and copying it down.

Now I wish to modify the formula to:
=if(A1/A4*100>150,"E",if(A1/A4*100>110,"e",if(A1/A4*100<66,"I",if(A1/A4*100<66,"i","-"))))

A long and tedious copy and paste procedure. I really don't want to do this 90 times!
As I said I created a macro which goes through the process of altering the formula but it changes the cell references when I apply it. Is there a way to do a macro that changes the formula keeping the cell refs intact?

Thanks

[raypayette]

When you want to keep a constant address, simply add a $ sign as in
$A$4 or $A4 or A$4
then copy your formula!

[Crispy1974]

Hi Ray,

Thanks for the assistance but unfortunately that can't work in my current situation. I've made a sample sheet illustrating my problem. The formula's I need to change are in cell D3-D6. The formula I want to change them to is in cell D2. Could you possibly see if you can do it with a macro (or other?) without altering the cell refs?

Thanks

Crispy

[Crispy1974]

If anyone could tell me that what I'm trying to do is possible or impossible, I would very much appreciate it. I'm about to plunge into 6 hours of tedious copying and pasting and if there's a shortcut it's going to save me from bashing my head against a wall every hour or so!