Method 'Range' of object '_Worksheet' failed error

[kualjo]

I have the following code that I'm using to validate survey responses. The defined range should have no zeros, and if it does, the msgbox will appear to let the respondent know that they skipped an item or items, as well as the item number(s).

Dim vCell As Range


Sheets("Calculations").Range("C4:C53").Name = "val_range"
For Each vCell In Range("val_range")
    If ActiveCell.Value = 0 Then
        msg = msg & ActiveCell.Offset(0, -2).Value & vbCr
        k = k + 1
    End If
Next vCell

I'm getting the error at the line

For Each vCell In Range("val_range")

I borrowed this code from an expert in another post while researching a different question, and only changed the particulars, so I'm not sure why it's failing. I suspect it may have to do with the fact that the sheet is initially hidden, though I'm unhiding it earlier in this procedure. I also attempted to activate the sheet first, but still get the error.

What am I missing?

[JKwan]

I do believe that in order to do anything..... your sheet must be visible, therefore, make the sheet visible first.

[p45cal]

Dim vCell As Range 
Sheets("Calculations").Range("C4:C53").Name = "val_range" 
For Each vCell In Range("val_range") 
    If vCell.Value = 0 Then 
        msg = msg & vCell.Offset(0, -2).Value & vbCr 
        k = k + 1 
    End If 
Next vCell

?

[SamT]

You don't need to add an item to the Sheet's list of Names

Dim vCell As Range 
Dim ValRange As Range 
 
Set ValRange =  Sheets("Calculations").Range("C4:C53")
For Each vCell In ValRange
    If vCell.Value = 0 Then 
        msg = msg & vCell.Offset(0, -2).Value & vbCR
        k = k + 1 
    End If 
Next vCell

[Paul_Hossler]

Try

For Each vCell In Range("val_range").Cells

If that works, it'll be the second time today I've seen the problem.

[kualjo]

Thanks all! Good responses from all and extremely helpful. Problem is solved, and I learned something new today!

[snb]

Or:

Sub M_snb()
  If [not(iserr(match(0,C4:C54,0)))] Then MsgBox Cells(3, 1).Offset([match(0,C4:C54,0)])
End Sub

[p45cal]

Or:

Sub M_snb()
  If [not(iserr(match(0,C4:C54,0)))] Then MsgBox Cells(3, 1).Offset([match(0,C4:C54,0)])
End Sub

snb, it only returns the first match!

[p45cal]

…however

MsgBox Join(Filter([transpose(IF(Calculations!C4:C53=0,Calculations!A4:A53,"¬!"))], "¬!", False), vbLf)

or

MsgBox Join(Filter([transpose(IF(exact(Calculations!C4:C53,0),Calculations!A4:A53,"¬!"))], "¬!", False), vbLf)

depending on whether you want blanks to be counted as zeroes.

[snb]

@p45cal

As soon as you have changed the first zeror, the next one will be shown in the msgbox.

[SamT]

@ Kualjo,

I like snb's approach of only reporting one cell at time better than one message with a list, because if there are many missing values, who can remember them all.

However I also prefer to highlight all the cells at once to show which ones are missing so they aren't pestered with popups. This is also similar to Web Page form actions.

Dim vCell As Range
Dim ValRange As Range
Missing = False
Set ValRange = Sheets("Calculations").Range("C4:C53")
For Each vCell In ValRange
If vCell.Value = 0 Then
vCell.ColorIndex = 22
Missing = True
End If
Next vCell

Add The Variable "Missing As Boolean" to the worksheet Module and add

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Removes warning color from Cell when Cell is selected
If Not Missing Then Exit Sub
If Target.Interior.ColorIndex = 22 Then Target.Interior.ColorIndex = xlColorIndexNone
End Sub