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

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

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

"ML0940" wrote:


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

Hi Peter, that makes sense, kind of.
So, even if I defined the range and gave it a name; I still need to put ann
array in?
Also, wouldn't the array need to be right after the variable -
If Sh2bwWV(1, 1).Value 0 Then

The good news is that it does prompt me for the overwrite if there is a
value in any one of the cells in that range, however, it is stilll prompting
me even if there are no values in that range.

Do you know why ththat may be?

Also, I will go try the offsett method as well

Thank you,
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

Joel

That did not do it either but thank you
All of the values in the cell will be numeric as they are all currency, so
that will always be True.

This is the part If Sh2bwWV.Value 0 Then that is not working correctly
with both suggestions??

Mark

"Joel" wrote:

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

"ML0940" wrote:


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,
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

"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

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

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

Hi Peter
That got it!
Thank you for the explanations as well, they made a lot of sense.
I see how you were checking for the cell count in the named range.

I then tried this first:
For i = 1 To Sh2bwWV
If Sh2bwWV(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

but it did not work properly; that is likely because it is grabbing the cell
count, not looping through each cell in the range.

However, this method worked perfectly:
For Each c In Sh2bwWV
If c.Value 0 Then

That is when I said ah hah! That's it!
I needed to perform the value check on each cell in that range; which is
what I am doing in my original code:
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

It is just that named ranges are still fairly new to me but much more
effective then hardcoding a range.

I am primarly a VBA programmer with AutoCAD, but on occasion, I do need to
do some coding in Excel.

And lastly, here is what I went with and is working great:
For Each c In Sh2bw.Offset(0, 1) 'Offset Sh2bw by one column
If c.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 c

Now I can use offsett and do away with a named range that I did not really
need, that is Sh2bwWV.

Peter, thank you again for the help.

I have been looking at that off and on for months

ML

"Peter T" wrote:

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