Excel VBA: 2 dimensional array search, check to see if cell is empty

Question

I have a range of cells in excel with names of people on the left column and certain tasks (as numbers) on the top row, like a two dim array. All I want to do is be able to search for the name and numbered task to see if that cell is blank or not in VBA.

In the excel sheet I can use a formula like: =ISBLANK(VLOOKUP("Bob", A2:AE33,3,FALSE))

which works, but can't seem to find do this in VBA.

I've tried to just do something like this:

Application.WorksheetFunction.ISBLANK(Application.WorksheetFunction.VLookup(studentComboBox.Value, Range("A2:AE33"), i + 2, False))

But it gives an error like "object does not support this property or method". Any help appreciated.

A relatively simple method would be to drop that formula into a cell offsheet where you can't see it normally. Then grab the value of that cell from VBA to get the calculated result from the formula. — mellamokb
Doug's answer is the right approach, but for completeness you can alternatively use ActiveSheet.Evaluate(yourFormula) where yourFormula is a variable containing a valid worksheet formula (including the "="). — Tim Williams

Doug Glancy Doug Glancy · Accepted Answer · 2012-08-30T18:02:19

You don't have access to ISBLANK via WorksheetFunction. Try: Application.WorksheetFunction.VLookup(studentComboBox.Value, Range("A2:AE33"), i + 2, False)=""

Excel VBA: 2 dimensional array search, check to see if cell is empty

1 Answers