Forum Discussion
Count Black Cells In a Range Using VBA
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
In the code as posted, you don't set the variable Current_Tasks to a specific range.
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!
Hi,
I think you forgot to set range for Current_Task
check this; maybe
Public Sub counterer()
'I declare theRange as rangeDim theRange As Range
'I then assign the range of cells to my variable
Set theRange = Range("A2:B11")
'I the return the number of blank in an active cell on my excel
ActiveCell.Value = WorksheetFunction.CountBlank(theRange)
End Sub
