what is the correct syntax for "If 3 < hit < 6 Then"

If (3 < hit) And (hit < 6) Then


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com




"Adam Kroger @hotmail.com" <adam_kroger<nospam wrote in message
...
UDF has been rewritten:

it now compiles, but returns 0
what is the correct syntax for "If 3 < hit < 6 Then"


Function con_check(con_old As Integer, con_now As Integer)
Dim i As Integer
Dim targ As Integer
Dim hit As Integer
Dim roll As Integer

hit = con_old
If con_old < con_now Then
For i = 1 To con_count
hit = hit + 1
If hit < 3 Then
targ = 1 + hit
End If
If 3 < hit < 6 Then
targ = hit + 6
End If
If hit 5 Then
con_check = "DEAD"
Exit Function
End If
If Application.RoundUp(Rnd() * 6, 0) +
Application.RoundUp(Rnd() * 6, 0) targ Then
myCell.Value = "PASS"
Else: con_check = "FAIL"
Exit Function
End If
Next i
Exit Function
End If
End Function


"Bob Phillips" wrote in
message ...
There are loads of problems here

- con_count is not defined
- what is mycell
- 3 < hit < 6 doesn't do what I think you think it does
- there is a Exit function after a Next (redundant)
- a missing End If

What is it trying to do (apart from the obvious bit about two
randoms).

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Adam Kroger @hotmail.com" <adam_kroger<nospam wrote in
message
. ..
This is my first time writing a new UDF :)

The error reported by debug is: "Block IF without End IF"
the highlight is on "next i"


Function con_check(con_old, con_now)
Dim i As Integer
Dim targ As Integer
Dim hit As Integer
Dim roll As Integer

If con_old < con_now Then
For i = 1 To con_count
hit = hit + 1
If hit < 3 Then
targ = 1 + hit
If 3 < hit < 6 Then
targ = hit + 6
If hit 5 Then
myCell.Value = "DEAD"
End If
End If
roll = Application.RoundUp(Rnd() * 6, 0) +
Application.RoundUp(Rnd()
* 6, 0)
If roll targ Then
myCell.Value = "PASS"
Next i
Exit Function
Else: myCell.Value = "FAIL"
Exit Function
End If
End If
End Function