If Value is > 0, in a named range ??

[ML0940]

Hi,
This topic has likely been covered a 100 times in this forum but I have been
converting all of my hard coded ranges into named range for the sake of
dynamic use.

However, in this one instance, I am have a hell of a time; I keep getting a
type mismatch.

Does anyone see anything wrong with the below code?

Dim Sh2bwWV As Range

Set Sh2bwWV = Range("Sh2billsWValues")

If Sh2bwWV.Value > 0 Then
If MsgBox("Do you want to overwrite last week's bills?", vbQuestion +
vbYesNo, "Accounts") = vbYes Then
GoTo Continue
Else
Range("A1").Activate
Exit Sub
End If
End If

------

Also, I am trying the offset method with another named range; this method
works when I copy or move an ofsetted range but not in the below example.
Where I am going wrong?

Any help is appreciated.

Thanks!
ML



If Sh2bw.Offset(0, 1).Value > 0 Then
If MsgBox("Do you want to overwrite last week's bills?", vbQuestion +
vbYesNo, "Accounts") = vbYes Then
GoTo Continue
Else
Range("A1").Activate
Exit Sub
End If

 

[Peter T]

If your named range refers to more than one cell change

If Sh2bwWV.Value > 0 Then

to
If Sh2bwWV.Value(1, 1) > 0 Then

also

If Sh2bw.Offset(0, 1).Value > 0 Then

to
If Sh2bw(1, 1).Offset(0, 1).Value > 0 Then

If the named range really only refers to a single cell, one reason for the
error would be if it contains a variable type that will fail when comparing
0, eg an error value

Regards,
Peter T

 

[Joel]

If isnumeric(Sh2bwWV.Value) then
If Sh2bwWV.Value > 0 Then
If MsgBox("Do you want to overwrite last week's bills?", vbQuestion +
vbYesNo, "Accounts") = vbYes Then
GoTo Continue
Else
Range("A1").Activate
Exit Sub
End If
End If
end if