AutoFilter doesn't always return value set as default

[mhelberg]

I am very new to VBA code and I have written the following to filter by the selected criteria, copy/paste into another sheet, or if the selection is not found then to clear contents and stop. I wanted to have the top value in the listbox selected by default, however, the autofilter doesn't always find the default value to return even though it is the same for all listboxes.

Please Login or Register  to view this content.

The criteria in field 2 is selected from listboxes with the following code:

Please Login or Register  to view this content.

I have been unable to figure out why this is happening, any ideas?

[Norie]

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[mhelberg]

Filtering VBA.xlsm

I have attached the file. When running the program you can see that the Criteria1:=L1S1FROM.Value for the different listboxes does not find the value and therefore can't return anything.

Although if you manually click on the first item in the list which should have been selected by default then it always finds the value.

Thanks for looking.

[Norie]

Does this work any better?

Please Login or Register  to view this content.

Note I deleted all the declarations for listboxes in the Initialize event as they aren't needed and could in fact cause confusion.

[mhelberg]

Norie,

Thank you for your reply, that seems to have done the trick! Would you be able to explain why I was having that issue and how that solved it?

[Norie]

To be honest I couldn't figure out why that was happening so I tried another method of getting the values.

The method I tried, using List and ListIndex, is what I would usually use.

What might have been the problem was the declarations you had for the listboxes which, as I mentioned, aren't needed.

Might be worth trying the original code without those declarations to see if that makes a difference.

[mhelberg]

Thanks Norie,

I am now trying to create code that will allow me to pull up a listbox (for example: L1S1FROM), hold a selection from that box constant, and cycle through all of the values from another listbox (example: L1S1TO), each time hitting the execute button to generate a result. Any thoughts on how it might be best to do that?

Thanks.