nested If between x and y then.. in vba

alekmaxwell · Jan 27th 2005

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!

Aaron Blood · Jan 27th 2005

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:

Code

'Using an If Then structure.
'Between 60 and 75  (non-inclusive)
If x > 60 and x < 75 Then
    y = 0.3
End If
'Between 150 and 180  (non-inclusive)
if x >150 and x <180 Then
    y = 0.66
End If
'and so on...




'Using a Select Case structure.
Select Case x
     Case 60 to 75 '(inclusive)
          y = 0.33
     Case 150 to 180 '(inclusive)
          y = 0.66
     Case 250 to 365 '(inclusive)
          y = 1
     Case Else
          y = 0.5
End Select

Display More

alekmaxwell · Jan 27th 2005

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

Barrie Davidson · Jan 27th 2005

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

What about

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?

alekmaxwell · Jan 27th 2005

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!

nested If between x and y then.. in vba

Participate now!

Share