If Value is 0, in a named range ??
"ML0940" wrote:
Peter,
I just tried the offsett method that you suggested and that did not do it
either.
Then I tried this -
Sh2bw.Offset(0, 1).Select
If Sh2bw.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
In theory, it should work as I am offsetting 1 column and 0 rows., selecting
that range abnd checking the entire range (sum total) for 0
Again, it works if there are values in any one of the cells but if there are
no values, I am still getting prompted with my msgbox.
Assuming that named range and offsett are working fine, as confirmed on
screen,
Here is what I need the final result to work like, only with my named range.
Dim i as Integer
For i = 6 To 24
If Range("N" & i).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
Next i
The above scenario has always worked fine but I need the named range to
work, in case the user inserts a new row.
Do I possibly need to loop through each cell in the named range?
Thanks
ML
"Peter T" wrote:
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
"ML0940" wrote in message
...
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
|