VBA works on Windows Excel 2010 but not Mac Excel 2011

[andyfleisher]

I modified the workbook from this site,
http://www.excelhero.com/blog/2010/07/excel-partial-match-database-lookup.html
to work with data from my job and added new worksheets to search the same data in different ways. On a windows machine running Office 2010, the macros work fine and the search results are displayed.

On a Macintosh running Office 2011, I get a runtime error 424 Object Required (I think) for the line,

[filter_criteria] = .Transpose(Array(CRITERIA_HEADER, "*" & [search_string] & "*"))

where [filter_criteria] and [search_string] are defined names in the workbook and CRITERIA_HEADER is a constant in the macro.

I am not a VBA programmer in any way, which is why I was using this wonderful code but I do know that the Mac and Windows versions of Excel use slightly different versions of VBA. What I do not know is if there are shortcuts being taken with the code which work OK for Windows but maybe on the Mac, the correct syntax needs to be used. For a check, I also tried the code on an older version of Excel 2004 on the Mac and I still got an error but at the next line,

[database].AdvancedFilter xlFilterCopy, [filter_criteria], [result_target]

Which made me think that maybe the Advanced Filter was not available (but it was when I searched through the menus) or that scripting for it was not available in the older version of Excel/VBA on the Mac.

The purpose of my file is not super important and if I can't get it to work on Macintosh computers it is not a big deal but I am just curious as to why is breaking between the two Excel versions. Thanks for all the help. I get all my Excel tricks from the MrExcel podcasts, which I love.

Andy Fleisher
UC Davis Graduate School of Management