Check if a Named Range in Array is blank

[Emeng]

Hi all

I have created seven Named Ranges for use in formulae in following procedures and want to check that none of them is blank. I have tried variations of the following but have bugged out on the If Application.CountBlank line each time.

With Sheets("Parameters")
   
        Range("G7").Name = "lMat"
        Range("H7").Name = "hMat"
        Range("G8").Name = "lAss"
        Range("H8").Name = "hAss"
        Range("G9").Name = "lEqu"
        Range("H9").Name = "hEqu"
        Range("G11").Name = "Prefix"
       
        If Application.CountBlank Range(Array("lMat", "hMat", "lAss", "hAss", "lEqu", "hEqu", "Prefix")) = 0 Then
        Call CheckForData
        Else
        MsgBox "Information missing from Parameters, please complete"
        End If
       
    End With

This arrangement causes a Compile error: expected Then or GoTo
.Range causes a run time error "Invalid number of arguments"

Any help to get me going is much appreciated.

Thanks & regards

Mark

 

[Hui]

Try

       If Application.worksheetfunction.CountBlank( Range(Array("lMat", "hMat", "lAss", "hAss", "lEqu", "hEqu", "Prefix")) )= 0 Then
       Call CheckForData
       Else
        MsgBox "Information missing from Parameters, please complete"
       EndIf

 

[Emeng]

Try

       If Application.worksheetfunction.CountBlank( Range(Array("lMat", "hMat", "lAss", "hAss", "lEqu", "hEqu", "Prefix")) )= 0 Then
       Call CheckForData
       Else
        MsgBox "Information missing from Parameters, please complete"
       EndIf

No luck Hui

This code causes a Range of Object_Global failure

Regards

Mark

 

[Marc L]

Hi,

the reason why an attachment is clearly needed as per forum rules …​

 

[Emeng]

Hi Marc

See attached. Thanks for your interest.

Regards Mark

 

[Chihiro]

As far as I know, COUNTBLANK only accepts 1 contiguous range as it's argument. You'll need to do something like below.

Assuming that there are more named range within the workbook... and no other named range exists in Sheets("Parameters").

Sub Demo()
Dim nRange As Name
Dim x as Long
With Sheets("Parameters")
        .Range("G7").Name = "lMat"
        .Range("H7").Name = "hMat"
        .Range("G8").Name = "lAss"
        .Range("H8").Name = "hAss"
        .Range("G9").Name = "lEqu"
        .Range("H9").Name = "hEqu"
        .Range("G11").Name = "Prefix"
    
        For Each nRange In ThisWorkbook.Names
            If InStr(nRange.RefersTo, "Parameters!") Then _
                x = x + Evaluate("=COUNTBLANK(" & nRange.Name & ")")
        Next
        If x = 0 Then
            Call CheckForData
        Else
            MsgBox "Information missing from Parameters, please complete"
        End If
  
    End With
End Sub

Edit: I just noticed, you were missing "." in front of Range(). You will also need it to ensure named range is added to appropriate sheet, when you have another sheet active.

 

[Marc L]

Yes many things we can't check without a correct attachment !

But creating names for fix ranges is not needed …
CheckRange demonstration in this thread.
​

 

[Emeng]

As far as I know, COUNTBLANK only accepts 1 contiguous range as it's argument. You'll need to do something like below.

Assuming that there are more named range within the workbook... and no other named range exists in Sheets("Parameters").

Sub Demo()
Dim nRange As Name
Dim x as Long
With Sheets("Parameters")
        .Range("G7").Name = "lMat"
        .Range("H7").Name = "hMat"
        .Range("G8").Name = "lAss"
        .Range("H8").Name = "hAss"
        .Range("G9").Name = "lEqu"
        .Range("H9").Name = "hEqu"
        .Range("G11").Name = "Prefix"
   
        For Each nRange In ThisWorkbook.Names
            If InStr(nRange.RefersTo, "Parameters!") Then _
                x = x + Evaluate("=COUNTBLANK(" & nRange.Name & ")")
        Next
        If x = 0 Then
            Call CheckForData
        Else
            MsgBox "Information missing from Parameters, please complete"
        End If
 
    End With
End Sub

Edit: I just noticed, you were missing "." in front of Range(). You will also need it to ensure named range is added to appropriate sheet, when you have another sheet active.

Hi Chihiro


Thanks for your help, your code works perfectly.


I have done some quick study on InStr, RefersTo & evaluate… there is so much more to learn.


Thanks & regards


Mark

 

[Emeng]

Yes many things we can't check without a correct attachment !

But creating names for fix ranges is not needed …
CheckRange demonstration in this thread.
​

Hi Marc


I see your point about not needing names for fixed ranges but when I look at this in 2 or 5 years’ time it will be easier to understand the formulae.


Others will use this tool and may also help them if they should need to change it for their particular circumstance.


Thanks for your CheckRange info, it is useful to know.


Regards


Mark