Before getting applying to your routine look at the following

If Sh2bwWV.Count = 1 Then ' just a single cell
myVal = Sh2bwWV.Value
Else ' 2 or more cells
myVal = Sh2bwWV(1).Value
' which is same as
myVal = Sh2bwWV(1, 1).Value
' which is same as
myVal = Sh2bwWV.Cells(1, 1).Value
'or
vArr = Sh2bwWV.Value ' a 2d array
myVal = vArr(1, 1)
End If

The point is, if the range refers to two or more cells its Value property is
a 2d variant array. But you can refer to any single cell in the multicell
range rather than using an additional array. The example refers to the first
cell in the range.

Now your code below

For Each c In Sh2bwWV
If Sh2bwWV(i, 0).Value 0 Then

if you want loop cells in Sh2bwWV

For Each c In Sh2bwWV
If c.Value 0 Then

the above is fine but for interest you could also do it as follows (if the
range is a single area of cells)

for i = 1 to Sh2bwWV
if Sh2bwWV(i).value then

Regards,
Peter T







"ML0940" wrote in message
...
WOW
I am really grassping for straws now
I just tried the below metho and the same thing, if no values are in the
named range, I am still getting prompted with the msgbox?

With this method, I was hoping to loop through each cell (rows) in the
named
range.
I can not believe that this is that difficult

Thanks again
ML

i = 0
Sh2bwWV.Select
For Each c In Sh2bwWV
If Sh2bwWV(i, 0).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
i = i + 1
Next c

"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