Method 'range' of object '_worksheet failed error on a simple formula and code

[Skysurfer]

Hi,

In a module, I have the following:

IF Sheet4.Range("TheList_CountBlanks").Value > 0 Then
Msgbox "Helpful message."
Exit Sub
End If

That range name uses the function COUNTBLANK on a simple range and is successfully used in formulas. But that code keeps blowing up with that error.

What am I doing wrong?

Lawrence

 

[DanteAmor]

Try this, The following is to obtain the result of the formula.

  If [TheList_CountBlanks] > 0 Then
    MsgBox "Helpful message."
    Exit Sub
  End If

 

[Skysurfer]

Thanks for the quick reply.

I tried your code and got the error message "Compile error: Can't find project or library."

Lawrence

 

[DanteAmor]

How did you create the name range and what do you have inside it?

 

[Skysurfer]

Really appreciate your help.

In the Name Manager, I created a named range entitled "TheList_CountBlanks" with the range "=COUNTBLANK(Intake_Form!$B$49:$B$63)". The function result = 1.

Lawrence

 

[DanteAmor]

In scoope did you put workbook?
Try:

If Evaluate("=TheList_CountBlanks") > 0

Another doubt:

Excel version?

 

[Skysurfer]

That worked!

Thank you.

But why do you think my OP and your 1st try didn't work? Did Evaluate force a special solution?

The range name scope is Workbook and I'm using Excel 365 ProPlus.

Lawrence

 

[DanteAmor]

That worked!

Thank you.

But why do you think my OP

It does not evaluate a formula, it shows the value of the cell.


and your 1st try didn't work?

For your excel version, I have 2007.

Hi