Count Black Cells In a Range Using VBA

[mtthomasm516]

I am trying to count blank cells in a range using VBA.  Below is an abbreviated version of my code.  The full macro contains more, but I figured all that is irrelevant at this point since it chokes on the line that attempts to set the value of My_Tasks, and that's basically the first thing that the macro needs to do.  At that point, I get "Run-time error '5': Invalid procedure call or argument."  I think that if I can get past that command, I can make the rest work.

Thanks in advance for any help that you can offer!

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim CursorColumn As Integer
    Dim My_Tasks As Integer
    Dim Current_Tasks As Range
    Dim Pending_Tasks As Range

    My_Tasks = WorksheetFunction.CountBlank(Current_Tasks)

End Sub

--Tom 

[#2]

@mtthomasm516

In the code as posted, you don't set the variable Current_Tasks to a specific range.

[#3]

@Hans Vogelaar 

Once upon a time I was incredibly good with Excel VBA, but that was way back in the Excel 4 days!  Haven't done a lot of it since then, but still, I should have seen that!  Current_Tasks is a named range in the worksheet.  I guess I was just thinking that the macro would somehow know that! 

[#4 · Accepted solution]

@mtthomasm516 

Try

    My_Tasks = WorksheetFunction.CountBlank(Range("Current_Tasks"))

[#5]

@Hans Vogelaar 

Thanks! I got it working with:

Set My_Tasks = Application.Range("B5:B9")

Normally, I don't like hard coding ranges, but in this particular case, that range will never change, so it works well enough.  Even so, I will see if I can get it working with the named range.