Autofilter - Macro to Return Value in Userform Field based upon Most Recent Date

Hi,

I'm using XL2007 and using autofilter to return a userform field value based upon the date in the userform field.

I have a macro that runs when a value is selected from a userform combobox. Based upon the CB selection the macro looks at the userform date field as a criteria for filtering, along with other columns, but the issue is on the date filter (col1, sheet1).

What I'm trying to do is return the most recent or update price for a species. The problem I have is that the current macro filters based upon the maximum date (todays date) and will only return a price for the value selected from the combobox if the price update date equals the userform date (todays date). So if I make a selection from the combobox that was not updated today, an error is returned.

I also tried

Please Login or Register  to view this content.

but this returns more than one record in the filter instead of only the most recent.

Assistance with this problem would be greatly appreciated as I've exhausted all possible iterations of macro code based upon my skill level.

TIA

hi Southfish, this would filter your data for 27 Apr 2012 and earlier:

Please Login or Register  to view this content.

AphaFrog

That's perfect!!!

I have about four forms where I need to display a current price before user updates price to sheet as well as a couple forms where the current/most recent price is used for $/lb x lb = $.

Thank you so much!!!

AlphaDog and Others,

Had to remove the follow from the code

Please Login or Register  to view this content.

as the example I posted may have been a bit misleading as the next step in using this filter was to write data to a sheet, therefore the "sDate" field on the userform was not intended to change. Not a problem as code filters data that I'm looking to return.

In incorporating with the master file I did run into a bit of a hiccup though when writing to the sheet through a "enter" button. The return price based on the code you supplied is not written to the sheet where the filter was applied but a second sheet with additional data field values that did not appear on form in test file.

I think I have been having this problem when I attempted to run macros from a combobox change event and then clicking an enter button where the data is written to the table okay, but you cannot clear the combobox field in the "click" event or an error occurs in line

Please Login or Register  to view this content.

.

It is like it loops back because if you clear the combobox it appears to be equivalent to a change event. Ideally, I'd like to clear the combobox after the click event if this is possible. Even if I set autofilter to off in your code, the looping still occurs in the "click" event unless I do not clear the combobox value in the "click" event.

Is it possible to clear the combobox field in the "click" event without looping back into the filter and getting ack into and hung up on the line of code above?

TIA

Originally Posted by Southfish

Is it possible to clear the combobox field in the "click" event without looping back into the filter and getting ack into and hung up on the line of code above?

This illustrates how the code will only filter when the combobox value is an item from its list.

Please Login or Register  to view this content.

AlphaDog,

I attached a second test file so you can see what the problem is and where it occurs. As indicated above, looks like Excel initiates the change event code again when a click event for the form is started.

Please note that in the attached example, the code is now in the "market" field.

TIA

AlphaDog,

Looks like I've solved it.

In the click event, you cannot clear the value in the combobox, "Market" CB in the file Test2 until just before you show the userform form again...See second last line in following code. Thanks again for your assistance and correcting the errors in the autofilter!

Please Login or Register  to view this content.