I would like to ask how to set a VBA to search the transactions for a particular item in a specific time?
I would like to ask how to set a VBA to search the transactions for a particular item in a specific time?
Hi..
Perhaps make a bit more of an effort with your post.. an example Workbook.. some sample code.. at the minimum a more detailed explanation of what you want.. ?
Sorry. OK. Let me make it more clearer..
I am doing a snack food report..
there are lots of snack food code number, e.g.ABCD01,MDAI03,CDBA04 etc
for examples, I would like to search all the transaction of ABCD01 (whether is received or supplied) in last month.
Am I clearer now?
Hi..
Assuming you have a column for the snack food code number and separate columns for the Dates and received/supplied condition.. sounds like you just need to add a Filter to your header row..
http://www.wikihow.com/Add-a-Filter-in-Excel-2007
Thanks.
But I want to use VBA to search
K.. no worries.. upload a sample workbook or detail the structure of your worksheet(like what columns you have etc)
A sample workbook is best.
Here is my sample excel,
for examples, I need all transactions of code ABCD01 in June 2013,
how can I use VBA to search it?
thx
Hi..
Your Excel attachment is corrupt.. please resave and upload again...
I have re-uploaded the excel file.
The excel file have 2 sheets, it's just a sample
Thanks,Zecond
Hi..
Select your start date and end date via the Date pickers.. and press the "Search" button..
Press the "Reset" button to show ALL rows again..
Use this code..
Please Login or Register to view this content.
Thanks!
So, how to restrict the date format in the combo box?
Hi..
What Date Format do you want to see?
Long, Short, medium...?
Example:
06/05/2012
5-Jun-12
Tuesday, June 5, 2012
06/05/2012
Hi..
I do believe the Date Format that was showing in the Datepicker combo box(in the file i sent back to you).. is Short Date format (which is the one you want)..
If not.. go to the Developer Tab> Design Mode> and right click on Date Picker and change its Format Property..
Thanks, but is it easy to write a programme (e.g.VBA) to help searching the transaction for a particular item in a specific time?
Hi..
You could just add another filter..
In the code below.. it is looking for the Product you type into cell C11..
I would probably use a combobox/listbox or Data validation list so the User just selects the Product they want to search for..
This fixes the issue of people spelling Products incorrectly..
Please Login or Register to view this content.
But what do DTPicker1 and DTPicker2 mean?
What are the code of DTPicker1 and DTPicker2?
As I see that there are lots of different code in website, but I will try to differentiate them
Hi..
DTPicker1 and DTPicker 2 are ActiveX controls.
Make sure your Developer Tab is enabled (go to the Options to do that)..
You can insert a Date Picker by going to: Developer Tab> Insert > ActiveX Controls> More Controls (little icon with a hammer and spanner) and then selecting the "Microsoft Date and Time Picker Control 6 (SP6).
So.. for example.. the following line of code is setting the "StartDate" variable to be what is selected in the Date Picker that is called DTPicker1.
Please Login or Register to view this content.
Re apo
thanks!! But I dun know why I input the date(10/9/2013), it automatically output to (9/10/2013), so that the system cannot search the date correctly.
So how can I correct it?
Hi..
Change the date formatting.. Change "mm/dd/yyy" to "dd/mm/yyyy".
Example:
Change this:
to this..Please Login or Register to view this content.
Please Login or Register to view this content.
Re apo,
I have changed it already, but it still output the wrong date format
Also, I wanna know how to print these data if I hv search it already.
I have successfully output the correct date format,
and I wanna know do I need to make a save button to save the data I have added in the form or is it fine to add them in the database only?
also, I dun know how to make a search function on the menu page, and use it to search the database,
then print the data
is it simple to do or not?
I have successfully output the correct date format,
and I wanna know do I need to make a save button to save the data I have added in the form or is it fine to add them in the database only?
also, I dun know how to make a search function on the menu page, and use it to search the database,
then print the data
is it simple to do or not?
I wanna know do I need to make a save button to save the data I have added in the form or is it fine to add them in the database only?
also, I dun know how to make a search function on the menu page, and use it to search the database,
then print the data
is it simple to do or not?
I wanna know do I need to make a save button to save the data I have added in the form or is it fine to add them in the database only?
Also, I have found someone was doing the similar question, and I have changed the code a little bit, but still cannot run it.
Hi..
I got your autofilter to work again... i think it didn't work for you because you messed around with the date formats again..
I don't know what you mean when you say..
I wanna know do I need to make a save button to save the data I have added in the form or is it fine to add them in the database only?
Hi,
yes....I dun know why I enter the data in the userform, then automatically output the text format, not the date format.
I have solved the problem of
"I wanna know do I need to make a save button to save the data I have added in the form or is it fine to add them in the database only?"
Thanks a lot.
I dun know how to use this code to search an unknown period.
For examples, when one user want to search 1.2013 to 4.2013 (1 means Jan,4 means April),Please Login or Register to view this content.
and the other want to search 3.2013-5.2013.
How can I write the suitable VBA for them to retrieve the suitable data??
I dun know why I enter the data in the userform, then automatically output the text format, not the date format.
How could I solve it
I dun know why I enter the data in the userform, then automatically output the text format, not the date format.
How could I solve it
I dun know why I enter the data in the userform, then automatically output the text format, not the date format.
How could I solve it?
I dun know why I enter the data in the userform, then automatically output the text format, not the date format.
How could I solve it?
I dun know why I enter the data in the userform, then automatically output the text format, not the date format.How could I solve it?
I dun know why I enter the data in the userform, then automatically output the text format, not the date format.
How could I solve it?
I dun know why I enter the data in the userform, then automatically output the text format, not the date format.
How could I solve it?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks