problem working with CountBlank Function in VBA

[megahurtz]

Hi All,

My Macro opens an excel file and I've to count the number of blank spaces for every filled column. I'm using CountBlank function for that.
But, the code fails at the countBlank line by saying Type Mismatch.

I've attached a snippet of the code below. Please tell me where I'm going wrong.

Sub Snippet_Code()
 
Dim apdata as excel.application, wbdata as workbook
 
set apdata = new excel.application
Set wbData = apData.Workbooks.Open(Filename:="ABCD.xlsx")
 
For colnum = 1 to 50         'Columns from 1 to 50
 
    lrD = wbData.Sheets(1).Cells(wbData.Sheets(1).Rows.Count, colnum).End(xlUp).Row               'Find last row for every column
 
    BlnkCnt = WorksheetFunction.CountBlank(wbData.Sheets(1).Range(Cells(1, colnum).Address, Cells(lrD, colnum).Address))     'Find number of blank cells in the provided range
 
Next colnum
 
End Snippet_Code

My code fails at the BlnkCnt part. I also tried assigning the two cell addresses in two variables and passing the variables to the range method. But there again, I get the error of Method Range of class _Global Failed.

What might be the error?
Thanks a lot in Advance!!

 

[Richard Schollar]

Try amending the code to:

For colnum = 1 to 50         'Columns from 1 to 50
  With wbData.Sheets(1) 
    lrD = .Cells(.Rows.Count, colnum).End(xlUp).Row               'Find last row for every column
 
      BlnkCnt = WorksheetFunction.CountBlank(.Range(.Cells(1, colnum), .Cells(lrD, colnum)))     'Find number of blank cells in the provided range
   End With
Next colnum

 

[megahurtz]

Still failing...

Still the same old run-time error of Type mismatch...

 

[Richard Schollar]

Did you copy the code in above or amend your original? Just wondering if a typo may have crept in

 

[megahurtz]

The code posted above was just a small snippet. I simply wanted to focus on the error area.
But even in my code, I implemented the WITH logic. Still it's giving me no luck.

This CountBlank function will work very nicely on the existing workbook. However, it's failing big time on external book. Please guide....

Thanks a lot in Advance!

 

[Richard Schollar]

What about if you reference the Worksheetfunction with apdata:


BlnkCnt =
apdata.WorksheetFunction.CountBlank(.Range(.Cells(1, colnum), .Cells(lrD, colnum)))

 

[megahurtz]

Finally it worked!!!

Thanks a lot Richard, You're a life-saver!!!!