I want to check the value that the user has entered by comparing to a lookup
table called 'CWTable'

Below is the code.
My problems:
- If I define CheckWeightMIN & MAX as integers, I get a type mismatch error
at the VLOOKUP step (note I remove .value in the IF statement if I use
integer)
-If I define CheckWeightMIN & MAX as variants, then I get a type mismatch at
the line IF CheckWeight...
- If I add .value as seen below, I get a error 424.

Help!!
Thanks.

Mark
-----------------------
'Check for Crazy Weight
Dim CrazyWeight As Boolean
CrazyWeight = False
Dim CheckWeight As Integer
Dim CheckWeightMIN As Variant
Dim CheckWeightMAX As Variant
Dim CheckAge As Integer
Dim Response

If Optkg = "True" Then
CheckWeight = PtWeightBox.Value
Else
CheckWeight = PtWeightBox.Value / 2.2
End If
If OptYears = "True" Then
CheckAge = PtAgeBox.Value
Else
CheckAge = PtAgeBox.Value / 12
End If

CheckWeightMIN = Application.VLookup(CheckAge, "CWTable", 2, True)
CheckWeightMAX = Application.VLookup(CheckAge, "CWTable", 3, True)

If CheckWeight CheckWeightMAX.Value Or CheckWeight < heckWeightMIN.Value
Then
CrazyWeight = True
End If

If CrazyWeight = True Then
Response = MsgBox("You have entered a weight of " & CheckWeight & "kg
for your " & CheckAge & " year old patient. This weight is out of range. Is
this correct? Click Cancel to re-enter", vbOKCancel, CrazyWeight)
If Response = "Cancel" Then
Exit Sub
Else
CrazyWeight = False
End If
End If

"CWTable" is a string. To make it a range that can be Lookin, it need to
be'

Range("CWTable")

ex:
Dim CheckWeight As Long
' vlookup can return and error so these should be variant
Dim CheckWeightMIN As Variant
Dim CheckWeightMAX As Variant

Dim CheckAge As Long

CheckWeightMIN = Application.VLookup(CheckAge, Range("CWTable"), 2, True)
CheckWeightMAX = Application.VLookup(CheckAge, Range("CWTable"), 3, True)

if iserror(CheckWeightMin) or iserror(CheckWeightMax) then
msgbox "Problems looking for value"
exit sub
End if



typo he
CheckWeight < heckWeightMIN.Value

--
Regards,
Tom Ogilvy



"sneagle" wrote in message
...
I want to check the value that the user has entered by comparing to a
lookup
table called 'CWTable'

Below is the code.
My problems:
- If I define CheckWeightMIN & MAX as integers, I get a type mismatch
error
at the VLOOKUP step (note I remove .value in the IF statement if I use
integer)
-If I define CheckWeightMIN & MAX as variants, then I get a type mismatch
at
the line IF CheckWeight...
- If I add .value as seen below, I get a error 424.

Help!!
Thanks.

Mark
-----------------------
'Check for Crazy Weight
Dim CrazyWeight As Boolean
CrazyWeight = False
Dim CheckWeight As Integer
Dim CheckWeightMIN As Variant
Dim CheckWeightMAX As Variant
Dim CheckAge As Integer
Dim Response

If Optkg = "True" Then
CheckWeight = PtWeightBox.Value
Else
CheckWeight = PtWeightBox.Value / 2.2
End If
If OptYears = "True" Then
CheckAge = PtAgeBox.Value
Else
CheckAge = PtAgeBox.Value / 12
End If

CheckWeightMIN = Application.VLookup(CheckAge, "CWTable", 2, True)
CheckWeightMAX = Application.VLookup(CheckAge, "CWTable", 3, True)

If CheckWeight CheckWeightMAX.Value Or CheckWeight < heckWeightMIN.Value
Then
CrazyWeight = True
End If

If CrazyWeight = True Then
Response = MsgBox("You have entered a weight of " & CheckWeight & "kg
for your " & CheckAge & " year old patient. This weight is out of range.
Is
this correct? Click Cancel to re-enter", vbOKCancel, CrazyWeight)
If Response = "Cancel" Then
Exit Sub
Else
CrazyWeight = False
End If
End If