# Formula help

#### deuce

##### Active Member
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

### Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

#### Michael M

##### Well-known Member
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

##### Active Member
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

##### Well-known Member
Yep, it does....but this may be quicker, bur change the ranges in the first aprt of the code to suit
VBA Code:
``````Sub MM1()
Range("A1:I50000").Formula = "=IF(AND(K3>=D\$41,K3<=E\$41),AZ\$2,"""")"
End Sub``````

#### deuce

##### Active Member

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

##### Active Member
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

##### Well-known Member

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

##### Active Member
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

##### Well-known Member
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
VBA Code:
``````Sub MM1()
Range("A1:D50").Formula = "=IF(AND(K3>=\$D41,K3<=\$E41),AZ\$2,"""")" 'change ranges to suit
End Sub``````

#### deuce

##### Active Member
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.

Replies
16
Views
206
Replies
7
Views
182
Replies
3
Views
710
Replies
4
Views
106
Replies
1
Views
326

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,152,116
Messages
5,768,211
Members
425,459
Latest member
Danniey

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back