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
– mellamokb, Commented Aug 30, 2012 at 17:59
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
– Tim Williams, Commented Aug 30, 2012 at 20:28

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

3

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

answered Aug 30, 2012 at 18:02

Doug Glancy

27.5k6 gold badges72 silver badges118 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related