Finding a Row in a Table – MyDGet (Excel 2003)

Topic

New Reply

Sometimes I’m asked to write code to find records in long tables with non-unique Keys. DGET with its access to a Criteria Table would be ideal except that it returns the value of a column and must be unique, ie only one row matches the criteria. Is there any clever and efficient way to get the row number instead? The ideal functionality would be something like

MyDGet(database,field,criteria,startrow)

where the first three arguments are as in DGET and the fourth indicates from where the search should start, thus allowing for multiple instances. I do most of my coding in the Macro 4 language and would very much appreciate assistance if VB is required.

Geoffrey Howell

Reply | Quote

Replies

Hi Geoffrey

You could use the DMIN Function as my attached sample shows and use the the Use of row numbers

Reply | Quote

But that doesn’t help if there are multiple records that satisfy the criteria, does it?

Reply | Quote

No it doesn’t…..does it?

Reply | Quote

I fear that’s a rather tall order. Perhaps it would be better to use a database program such as Microsoft Access.

Reply | Quote

Thanks to both of you. I think I’ll probably try to code up a primitive equivalent based on a one row Criteria Table with simple conditions (probably equal) only allowed.

regards

Geoffrey Howell

Reply | Quote

Good luck, that should be less difficult than trying to handle complex criteria. But I still think using the built-in features (Advanced Filter with the Copy To option) or moving to Microsoft Access would be better.

Reply | Quote