Excel VBA: Search Problems

This will work much better if you can post the meta-data (info about the layout / structure) of the table in the same way as I use in my example. Click on the Reply button and you will have access to all the codes I've used. PK & FK stand for Primary Key & Foreign Key respectively.
Table Name=[tblStudent]
It would also help to understand what you're after if you were to provide a few examples of how you expect this to work. I'm not at all clear on how you intend to use a multi-column filter. Would you enter the search items in three separate controls on your form? If not, what do you envisage?

Well, you can probably scratch all that as I now find that this question in the MS Access / VBA forum is actually an Excel question.

Let me rename the thread to something that makes better sense then I may have another look.

Right, now we all understand that you're talking about an Excel issue...

You need to explain what you're trying to achieve a lot better than you have.

Please provide a few examples of how you expect this to work. Would you enter the search items separately? If not, what do you envisage?

Right, now we all understand that you're talking about an Excel issue...

You need to explain what you're trying to achieve a lot better than you have.

Please provide a few examples of how you expect this to work. Would you enter the search items separately? If not, what do you envisage?

Yes, you would enter the three searches separately. When you click on a button it would pull up one box that said enter month when you enter the month and click ok another box would open that said enter year when you enter the year and click ok a final box would open that said enter technicians name when you did so and clicked ok the search for those three things would begin. The search would result in a match on one row (where a lot of other information is located in different columns). Then that entire row would be copied to another row where I have set links to make a report. I have made the boxes happen, but I don't know how to make the search actually run. I have no real training on macros only looking at examples that others have done and trial and error from there. If there is another way to search for those three things let me know, I am open to anything. I hope this better explains what I am trying to do.

Hi. I haven't got an immediate solution for you but at least we know more about what you need. The method used for your previous search relied on the data you are searching for being ordered in ascending order - otherwise VLOOKUP would not have worked. You can't easily use a variant of that approach this time because VLOOKUP (or its column-wise counterpart HLOOKUP) are single-value comparison functions, and finding a match in a row of the first column does not necessarily mean that you are near the right row at all when there are two other values to consider.

If it wasn't for your row copy and report linkage I would simply suggest you link the Excel worksheet to Access and use an access parameter query to find the matching record for you, but this won't do your copy and link bit for you.

Excel is an excellent financial spreadsheet but a very poor database - and perhaps you might be better off convincing your boss to let you use a real database to handle such work.

-Stewart

While showing a fairly in-depth knowledge of how VLookup() works Stewart, I think you may be mistaken about this particular call.

When Range_Lookup is set to TRUE, it is indeed true that sorted data is expected (if it's to work reliably). However, when set to FALSE it searches through the data until it finds an exact match, regardless of the order of the data.

Yes, you would enter the three searches separately. When you click on a button it would pull up one box that said enter month when you enter the month and click ok another box would open that said enter year when you enter the year and click ok a final box would open that said enter technicians name when you did so and clicked ok the search for those three things would begin. The search would result in a match on one row (where a lot of other information is located in different columns). Then that entire row would be copied to another row where I have set links to make a report. I have made the boxes happen, but I don't know how to make the search actually run. I have no real training on macros only looking at examples that others have done and trial and error from there. If there is another way to search for those three things let me know, I am open to anything. I hope this better explains what I am trying to do.

As I'm a little the worse for wear at the moment ;), I will indicate an approach that I use when either searching or sorting on multiple column values. If this isn't clear enough I can flesh it out when I'm more compos mentis.

Produce a column at the left of your data which is a composite of the three columns you need to search. For string data this is fairly straightforward (=B2 & C2 & D2 ==> copied down column A).

Simply then, search in this column for the string built up from the three values entered.

Let me know how you get on.

Oops. Sorry for that overlook. NeoPa is indeed correct - data does not need to be sorted for it to work as long as the range indicator is set false. I do too many reference tables which are always sorted, and confused myself here.

NeoPa's suggestion of combining the three columns is good, and indeed I think it is the only practicable way to achieve the outcome and retain the spreadsheet approach.

-Stewart

...When Range_Lookup is set to TRUE, it is indeed true that sorted data is expected (if it's to work reliably). However, when set to FALSE it searches through the data until it finds an exact match, regardless of the order of the data.

The copy function works great, but is there a way to make three input boxes, or do I just have to type the month, year, and tech's name all in the one box? Sorry that I made this more complicated than it was. Sometimes we get focused on looking for one answer that we don't see the more simply answer right in front of our face.

The copy function works great, but is there a way to make three input boxes, or do I just have to type the month, year, and tech's name all in the one box? Sorry that I made this more complicated than it was. Sometimes we get focused on looking for one answer that we don't see the more simply answer right in front of our face.

Hi

I'm not sure this will help, but if we are using code (in Excel!) rather that worksheet function, then I would tend to write my own code/functions.

I you know the columns that the particular data is in then perhaps somethin like this or similar may be of interest ??

Of course there are many ways of doing this, defining the search range etc., it just depend on layout/circumstances.


MTB

Interesting code Mike, but Excel search functions are highly optimised and can perform at many times the speed of VBA code checking each cell.

The copy function works great, but is there a way to make three input boxes, or do I just have to type the month, year, and tech's name all in the one box? Sorry that I made this more complicated than it was. Sometimes we get focused on looking for one answer that we don't see the more simply answer right in front of our face.

It's certainly possible for all three to be called from the same piece of code. It's not necessary for each to be triggered by separate events (button clicks etc).

I don't know what code you're using at the moment, but if you post it I can tell you how you can combine it into a single triggered routine.