OverFlow Issue with my macro

[Jedit]

For my code, I have the user insert a numerical value in row 1 column 6. For example the user may enter the value 45. The user then runs the macro which should spit out the number 124 into row 3 column 6; however, I am receiving an overflow error. I have posted the code below:
Sub CalculateAQI()
Dim I As Long, IL As Long, BPH As Long, BPL As Long, IH As Long, J As Long, X As Long, K As Long
Dim C As Long, V As Long
Dim L As Long
'

'User inputs a numerical value located in cell (1,6) and then runs this macro
Cells(1, 6).Value = I
If 0 <= I <= 15.4 Then
IH = 50 And IL = 0 And BPH = 12 And BPL = 0
Else
If 15.5 <= I <= 40.4 Then
IH = 100 And IL = 51 And BPH = 35.4 And BPL = 12.1
Else
If 40.5 <= I <= 65.4 Then
IH = 150 And IL = 101 And BPH = 55.4 And BPL = 35.5
Else
If 65.5 <= I <= 150.4 Then
IH = 200 And IL = 151 And BPH = 150.4 And BPL = 55.5
Else
If 150.5 <= I <= 250.4 Then
IH = 300 And IL = 201 And BPH = 250.4 And BPL = 150.5
Else
If 250.5 <= I <= 350.4 Then
IH = 400 And IL = 301 And BPH = 650 And BPL = 250.5
Else
If 350.5 <= I <= 500.4 Then
IH = 500 And IL = 401 And BPH = 650 And BPL = 250.5
End If
End If
End If
End If
End If
End If
End If
'The destination of the final numerical value should be placed in Cell(3,6)
J = (IH - IL)
K = (BPH - BPL)
X = (I - BPL)
L = CLng(J) / CLng(K)
C = CLng(L) * CLng(X)
V = CLng(C) + CLng(IL)

'Cells(3, 6).Value = ((IH - IL) / (BPH - BPL)) * (I - BPL) + IL
Cells(3, 6).Value = V
'
End Sub

My error occurs at L = CLng(j) / CLng(k). I originally has all values as integers and I attempted to use the help menu from Microsoft Excel; however, I have been banging my head against a wall for the last two days trying to make this work. If anyone could help me or provide me with some guidance, I would greatly appreciate it.

Jedit

 

[Norie]

Jedit

What are the values of j and k when you get the error?

Actually, no need to answer, just figured it out.

j and k are both 0 and that's why you get the error.

Why are you putting the value of I, which is 0, in Cells(1, 6).Value here?

Cells(1, 6).Value = I

Shouldn't it be the othere way round.

I = Cells(1, 6).Value

Also, logical statements like 0 <= I <= 15.4 don't work as you would think.

Instead of checking if I is between 0 and 15.4 that statement will check if I is more than or equal to 0 and return the True/False boolean for that.

Then it will compare True/False to 15.4

It should be written like this.

0 <= I And I <= 15.4

 

[Jonmo1]

A few problems...

The first line that's actually doing anything..

Cells(1, 6).Value = I
That is actually putting the value of I into Cells(1,6).
But I believe what you want is to take the value from Cells(1,6) and put it in the variable I
So it should be reversed.
I = Cells(1,6).Value


And all your IF structures are just way out of syntax..

This
If 0 <= I <= 15.4 Then
Would have to be written like
If 0 <= I AND I <= 15.4 Then

Then the action if true in each if should not be using the AND function..
It doesn't mean "also do this"..

You have to put each action if true on a seperate line..

so this

If 0 <= I <= 15.4 Then
    IH = 50 And IL = 0 And BPH = 12 And BPL = 0
Else

should be

If 0 <= I AND I <= 15.4 Then
    IH = 50 
    IL = 0 
    BPH = 12 
    BPL = 0
Else

Hope that helps..

 

[Norie]

PS Instead of all those Ifs try a Select Case.

Select Case I
    Case 0 To 15.4
       IH=50:IL=0:BPH=12:BPL=0

    Case 15.5 To 40.4
       IH=100:IL=51:BPH =35.4:BPL=12.1

    ' etc 
 End Select