 # nested If between x and y then.. in vba

• Hi all

I have the following code taken from some fantastic stuff from richie previously

Code
``"=IF(R[-1]C[-7]=DATE(2005,3,12),0.66,IF(R[-1]C[-7]=DATE(2005,6,25),1,0.5))"``

I have been able to use the code before for all sorts but am having trouble on swapping the above for an if statement that enters the following (in vba).

if ((R[-1]C[-7] - R[-1]C[-8])=between 60 and 75,0.3, if between 150 and 180, 0.66, if between 250 and 365, 1, otherwise 0.5

I tried finding 'between' on help but it was nowhere to be found? :?

Any help much appreciated once more!

• Re: nested If between x and y then.. in vba

In VBA there are at least two ways to handle it.

Assuming the formula result is assigned to the variable x:

[SIZE="1"]Sub All_Macros(Optional control As Variant)[/SIZE]

• Re: nested If between x and y then.. in vba

thanks Aaron. Although to be honest I am not sure how to take account of the fact that it depends on the result of the cell offset(-1, -7) minus offset(-1, -8)... Is there perhaps a normal excel function that can be used to do this and I can input that into the cell instead? (was hoping to have formula appear in the cell too you see...) sorry about that, hope it can be done!
A

• Re: nested If between x and y then.. in vba

Code
``````"=IF(AND(R[-1]C[-7] - R[-1]C[-8]>=60,R[-1]C[-7]" & _
"- R[-1]C[-8]<=75),0.3,IF(AND(R[-1]C[-7] - R[-1]" & _
"C[-8]>=150,R[-1]C[-7] - R[-1]C[-8]<=180),0.66,I" & _
"F(AND(R[-1]C[-7] - R[-1]C[-8]>=250,R[-1]C[-7] - " & _
"R[-1]C[-8]<=365),1,0.5)))"``````

Does that work for you?

Barrie Davidson
My Excel Web Page
"You're only given a little spark of madness. You mustn't lose it." - Robin Williams

• Re: nested If between x and y then.. in vba

I do believe that will do just the job Barrie! I have changed my test data unfortunately but from all accounts and purposes seems great!

Thanks both!

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!