Find specific data combination on another sheet using command button.

**Ataraxicatom** · 09-07-2012, 01:06 PM

Hello,

I have been working on something for a while now and I've hit a brick wall. I have a command button that I need to add functionality to. It needs to search a different sheet for a name/date combination. I know how to search for 1 at a time, but not both. Or make it search from the bottom going up. That would work for me as well. Then I could just find the last entry of the name and check the date on it. If you look at the sheet, you will see what I am working with. Basically, the button checks the cells to make sure data is entered, then it adds the pertinent data to the other sheet. I would like to have it check to see if the data is already there. Since the names will come up more than once, I cannot just search for that and since there will be multiple people per day, I cant search by date either, it has to be both.

Here is the code for the button:

Please Login or Register  to view this content.

Oh! and it's a location based dynamic button. It takes a value from the row it is on for most of the calculations and data transfer. The button can exist on any row on the sheet... makes it idiot proof... Kinda.

Any help would be appreciated.

**jaslake** · 09-07-2012, 02:07 PM

Hi ataraxicatom
Here's one way

Please Login or Register  to view this content.

**Ataraxicatom** · 09-07-2012, 03:40 PM

That works perfectly. Thank you very much. :D

**Ataraxicatom** · 09-07-2012, 04:22 PM

oops! I lied. Ok, so it only likes to accept something if the name is not already on the list.

**jaslake** · 09-07-2012, 05:11 PM

Hi ataraxicatom

Of course it does...that's what the code SAYS to do...sorry about that. Try this instead

Please Login or Register  to view this content.

**Ataraxicatom** · 09-07-2012, 06:12 PM

Ok, that works the way I wanted it to. Was it the scope of the variables? aside to the definition of LookCell it looks like the only changes you made was the "with loop" and changed the placement of my variables. I am not very good with VB (obviously)... I assumed that it was going to search for the date and name combination and throw the error if they were found together based on the way the variables were defined. Does offset allow it to check adjacent cells as defined: (0 rows, 1 column)? or should I just put my head down and take this generous gift of yours without question? ;P

you don't have to answer. I am just curious. Most of my programming experience is with C++.

Thanks!!!

**jaslake** · 09-07-2012, 07:59 PM

Hi ataraxicatom

You don't need to

just put my head down...without question

You're questions are on point. The ONLY significant change made was here

the definition of LookCell

and made the code perform as expected. The rest of the changes were "fluff", changes made to accommodate my writing style...change them back if you wish (or I can...if you wish).

This

Does offset allow it to check adjacent cells as defined: (0 rows, 1 column)?

does exactly as you describe.

If you want to know WHY this change

the definition of LookCell

made a difference I can explain...or, create two separate files, one with the original code and one with the new code. Step through the code (F8) and hover over the variables as they're defined...see what's happening. If you don't see it let me know...I'll explain.

Let me know how I can help.