Find feature in userform

Hi I have a userform which allows me to add,find, and update data in a worksheet.

When i try to find data by using numbers, such as 100 or so it finds all the entries. But when it comes to trying to find data with dates it says that there is no data matching that date.

Please Login or Register  to view this content.

Hi rayexcel,

It looks like you are trying to AutoFilter dates. See post #3 in the following thread which explains the special date format required, and solves a date AutoFilter problem. It includes an example file that you can download: http://www.excelforum.com/excel-prog...ain-point.html

Lewis

None of the code fails, it just returns a 'no data found' msgbox which i created to pop up when no data is there.

So for my find feature, will converting the the dates in my code to numbers when searching fix this issue?

LJMetzger, So i should use the cdbl function?

As in format my dates using vba? Instead of having the sheet already formatted for that column?

Currently, vba formats it into the short date format, and then the sheet is formatted already for that column in the short date format?

What kind of variable is rsearch

Hi this is the code ive attempted to adapt but still get no matches..

Please Login or Register  to view this content.

Still had no luck

I would upload the file but its too large, even when compressed

Ray,

If possible please upload a sample file that contains dates you are trying to match. It is easier to find a solution to a problem like yours, when working with the exact format of what you are searching for.

To upload a file, click on 'Go Advanced', underneath the reply window. Then click on the 'PaperClip Icon' above, or the 'Manage Attachments' CommandButton below the message window.

Lewis

Hi

I am either getting an applicaation defined/object defined error, or worksheet defined error.

with the following code:

Please Login or Register  to view this content.

Heres an examploe

Hi John, still same problem, and now the cmbAmend doesn't work either..

Thanks for the help though, the cmbDelete still works though

Ray,

Thanks for uploading the sample file. It was extremely helpful. My working file is attached. I was successful, however there may be additional work required due to the neverending fight between US and European date formats (mm/dd vs dd/mm). I started out by searching for 2/2 (same US and European Formats). When I searched for 1/3 and 3/1 my results were for the incorrect month and day. Hopefully, you will not have the mm/dd reversal problem. If you still have that problem, I may be able to suggest fixes, but I won't be able to test the code, because I am on the wrong side of the pond.

The following two references can probably explain what I did better than I can. One for .find and the other for AutoFilter with respect to dates.
'Find (.find) Reference: http://www.ozgrid.com/VBA/find-dates.htm
'AutoFilter Reference: http://www.ozgrid.com/VBA/autofilter-vba-dates.htm

To get .find to work, I made the following changes in red. The most important line is the line that contains 'Short Format'.

Please Login or Register  to view this content.

There were similar issues with AutoFilter. AutoFilter apparently has issues with European Dates. That is the reason for the two criteria approach to finding a single date. It has the added benefit (side effect) to be able to disregard times, if times were included in the date.

Please Login or Register  to view this content.

Lewis

Hi LJMetzger

From trying the userform:

• I get an error 'Invalid use of null' when I try to select one of the dates for '2/2/2014' listed in my listbox
• This is listed in the listbox sub and the line highlighted is the line:

Please Login or Register  to view this content.

• When I try find something with only one date there it finds and fills the second textbox correctly, but I cant update the values

The code works, that you added/changed but If im correct you haven't changed the code to the cmbAmend? So it should still work if im correct.

I think your code for dd/mm is correct and works, aside from in the listbox it displayed dates in the form mm/dd.

I tried another method, of when the userform loads the column with dates is changed to number format and the userform can find it this way using cdbl, then when the userfom unloads it changes it back to 'short date' format. But when the form unloads the format changes to mm/dd. Would this solution be efficient if I fixed that format mixup?

I didnt make my last point clear, the columns changes back to short date format after unloading. But if I have update values then those values changes to mm/dd format. This is likely to how i code the cmbAmend feature.

It looks like you are trying to put access column 5 of the list box, (.ColumnCount is 6), but we are only putting data in columns 1 and 2.

Please Login or Register  to view this content.

I think you are being a little unfair. The original question was 'Find feature in userform' which has morphed into a whole lot more.

Sorry thats understandable, I didn't mean to be so. Thanks for the help though very much appreciated