Formula help

[deuce]

Hi,


I'm looking for help with the Below formula.

(IF(AND(K3>=D$41,K3<=E$41), AZ$2,"") Formula has to be dragged all the way to the right hand side as far as right goes, and down about 50000 cells. For the time being I had to do each cell manually, including visual error checking and it's like ?

Thanks in advance and I appreciate the help

 

[Michael M]

Highlight the area that is going to contain the formulas, starting at the top left cell
Enter the formula and then press CTRL + Enter

 

[deuce]

hi thanks for responding. oops! i forgot to state that the formula for each cell would change accordingly, when dragged across and then down

 

[Michael M]

Yep, it does....but this may be quicker, bur change the ranges in the first aprt of the code to suit

Sub MM1()
Range("A1:I50000").Formula = "=IF(AND(K3>=D$41,K3<=E$41),AZ$2,"""")"
End Sub

 

[deuce]

so it would be
(IF(AND(K3>=$D$41,K3<=$E$41), AZ$2,"")
(IF(AND(K3>=$D$42,K3<=$E$42), ba$2,"")

 

[deuce]

hi, i have another sheet with the macro but it kept on crashing the sheet so i had to delete it to create and use the formula instead.

 

[Michael M]

How many columns / rows do you want to apply the formula to ?
The macro shouldn't crash unless you are going ALL the way across to the last column, because "AZ$2" dragged across would be off the end of the worksheet!!
The formula method will adjust for ANY range that is allowed to change....
=IF(AND(K3>=$D$41,K3<=$E$41), AZ$2,"")
So, if this has 2 absolutes($signs) it won't change
The AZ$2 will chnage the columns as required bot not the rows.
Does that make sense

 

[deuce]

yes that makes sense, the AZ2 is not the problem it's the middle of the formula that needs to be modified, meaning D and E column references should not change when dragged across, but would change only when dragged downwards(row numbers would change accordingly), so then when dragged downwards D41 and E41 would become D42 and E42.

 

[Michael M]

So, did you modify the required range in the code, then give it a try....that's what it does
Why not test it on a small range

Sub MM1()
Range("A1:D50").Formula = "=IF(AND(K3>=$D41,K3<=$E41),AZ$2,"""")" 'change ranges to suit
End Sub

 

[deuce]

na, no macros, they cause too many problems. I have 15 separate workbooks, none of the macros in them work. However, the ones with formulas however many they are they work flawlessly though.