+ Reply to Thread
Results 1 to 24 of 24

Vba code for userform to search by range of date

  1. #1
    Forum Contributor
    Join Date
    08-24-2012
    Location
    Portugal
    MS-Off Ver
    Excel 2003
    Posts
    119

    Vba code for userform to search by range of date

    Hi guys

    Before my question i just want to say that this forum and the people here are amazing ;-)

    Ok, now to the point...Can anyone give me a hand with a code to this sample file?
    I have one file of this every month. In this sample i just have 2 materials, and this controls the stocks. I have design a userform with what i want (if its possible), and it consists in a combobox where i can select the material (in this sample there are LA-48, and Sact, but there are plenty more...maybe 20 but i hope that with your help with a code i can just add the others). Then i have two calendars where i introduce the start date and in the other the end date to use as a range to give me the sum of the inīs or the outīs, if i press one or another of this buttons (In / Out).
    The result should appear in the textbox below and in the last textbox should appear the actual stock at the end date selected on the calendar (End). If its blank, or 0,or negative it should appear that way in the textbox.
    Thanks in advance

    Sample file.xls
    Last edited by bazofio; 05-14-2013 at 03:25 PM.

  2. #2
    Forum Contributor
    Join Date
    08-24-2012
    Location
    Portugal
    MS-Off Ver
    Excel 2003
    Posts
    119

    Re: Vba code for userform to search by range of date

    Anyone please

  3. #3
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Vba code for userform to search by range of date

    bazofio,

    When I try to launch the form, I get error: "Could not load some objects because they are not available on this machine."
    Screen shot attached.
    As a result, I can't tell you what's wrong with your code. I know that there is no Calendar control in Excel 2007+ so maybe that is the issue.

    Then when I took a look to see what code was in the userform, I found that there wasn't any code, so I'm a bit confused on what you need help with.
    Attached Images Attached Images
    Hope that helps,
    ~tigeravatar

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  4. #4
    Forum Contributor
    Join Date
    08-24-2012
    Location
    Portugal
    MS-Off Ver
    Excel 2003
    Posts
    119

    Re: Vba code for userform to search by range of date

    Thanks tiger for tour time :-)
    I didnt actually make any code. What i have done was just the userform and it was in excel 2000.

  5. #5
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Vba code for userform to search by range of date

    Alright, well I think what you're looking for is something like this:
    Please Login or Register  to view this content.

    EDIT: I feel it is important to note that the above code is untested because of the reasons I mentioned above
    Last edited by tigeravatar; 05-16-2013 at 01:51 PM. Reason: added edit

  6. #6
    Forum Contributor
    Join Date
    08-24-2012
    Location
    Portugal
    MS-Off Ver
    Excel 2003
    Posts
    119

    Re: Vba code for userform to search by range of date

    WoW
    Ok i just put this code and theres nothing to select in the combobox. I think im doing something wrong here but cant get there.

  7. #7
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Vba code for userform to search by range of date

    My code doesn't populate the combobox. How do you want it populated? From the 'Stocks' sheet or from the sheet names? Or do you have a range of cells somewhere that lists all of the materials that could be selected in the combobox?

  8. #8
    Forum Contributor
    Join Date
    08-24-2012
    Location
    Portugal
    MS-Off Ver
    Excel 2003
    Posts
    119

    Re: Vba code for userform to search by range of date

    The combobox should be populated with the sheet names, in that example there are two of them (LA-48 and Sact), but i have maybe 20 or so. When i have all of them they will be also in the stocks sheet in the rows below the others that are there now from where i hope i get all the inīs and outīs for each material selected in the combobox.
    And i forgot to create an Exit button in the userform, damn me lol.
    Last edited by bazofio; 05-16-2013 at 02:05 PM.

  9. #9
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Vba code for userform to search by range of date

    bazofio,

    Add this userform_initialize code to the userform. Make sure to update the red part with the sheet names that should not be included in the drop-down. All sheet names should be separated by a colon ":" in that part as shown (including starting and ending with a colon)
    Please Login or Register  to view this content.

  10. #10
    Forum Contributor
    Join Date
    08-24-2012
    Location
    Portugal
    MS-Off Ver
    Excel 2003
    Posts
    119

    Re: Vba code for userform to search by range of date

    Ok now they show up :-)
    But when trying to get the results i get a error message: Compile error Method or data member not found.
    This: Private Sub InOutResults(ByVal strAction As String) is yellow and this: (Me.Calendar1.Value) is grey.

  11. #11
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Vba code for userform to search by range of date

    Are you using calendar controls? Are they called Calendar1, Calendar2, etc? They didn't show up for me so I don't know what they're called or what kind of control you're actually using, so that was just my best guess.

  12. #12
    Forum Contributor
    Join Date
    08-24-2012
    Location
    Portugal
    MS-Off Ver
    Excel 2003
    Posts
    119

    Re: Vba code for userform to search by range of date

    That was it, i correct and its now working ;-)
    What is missing is the actual stock at the selected end date.

  13. #13
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Vba code for userform to search by range of date

    The code I provided was based on the example file you provided. I used textboxes to provide myself with start and end date inputs and ran a test and it got back the actual stock at the selected end date properly. Is the setup different on your actual workbook? If it is, are you certain that there is a stock price for that end date? in the sample file, there are only Actual Stock values through 13-May-2013.


    EDIT: lol, I apparently really like the word provide(d)
    Last edited by tigeravatar; 05-16-2013 at 02:33 PM. Reason: added edit

  14. #14
    Forum Contributor
    Join Date
    08-24-2012
    Location
    Portugal
    MS-Off Ver
    Excel 2003
    Posts
    119

    Re: Vba code for userform to search by range of date

    Lol its a "nice" word hehehe
    Ok i have try with various end dates (form day 2 to day 3 for example) and it didnt show up any Actual stock at the end date. Im using the same workbook that i uploaded, i have another one with all materials but im just testing this to adapt after when its working.

    Edit: its gives me always zero
    Last edited by bazofio; 05-16-2013 at 02:49 PM.

  15. #15
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Vba code for userform to search by range of date

    Try changing these lines:
    Please Login or Register  to view this content.

    To include the CDate() method:
    Please Login or Register  to view this content.

  16. #16
    Forum Contributor
    Join Date
    08-24-2012
    Location
    Portugal
    MS-Off Ver
    Excel 2003
    Posts
    119

    Re: Vba code for userform to search by range of date

    That didnt work tiger, its always showing zero.

  17. #17
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Vba code for userform to search by range of date

    Here's debug friendly version of the code. I have put in some messageboxes to help you narrow down where the disconnect is. Use the information that the messageboxes provide along with the new comments in the code to find the problem:
    Please Login or Register  to view this content.

  18. #18
    Forum Contributor
    Join Date
    08-24-2012
    Location
    Portugal
    MS-Off Ver
    Excel 2003
    Posts
    119

    Re: Vba code for userform to search by range of date

    I try that code but its worse. Now nothing appears, no results. Neither the inīs and outīs and neither the actual stock at end date. Not even any msgbox.

  19. #19
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Vba code for userform to search by range of date

    did you change the calendar1, calendar2 to what they're supposed to be?
    And no matter what, it should give you a msgbox, there's one for every possibility, unless the code errors out, in which case you should get an error message.

  20. #20
    Forum Contributor
    Join Date
    08-24-2012
    Location
    Portugal
    MS-Off Ver
    Excel 2003
    Posts
    119

    Re: Vba code for userform to search by range of date

    Yes i have chenge the calendar1 and 2 to DTPicker1 and 2. It was working ok the inīs and outīs with the other code. With this one i dont get anything, not even an error or a msgbox. Weird


    Edit: ok i try again just copy paste like the first time and dont know why it working now. It displays a msgbox with the end date selected and then another saying: end date not found
    Last edited by bazofio; 05-16-2013 at 03:31 PM.

  21. #21
    Forum Contributor
    Join Date
    08-24-2012
    Location
    Portugal
    MS-Off Ver
    Excel 2003
    Posts
    119

    Re: Vba code for userform to search by range of date

    This is getting weird. Now i didnt change anything and when trying again its not working. It says: Runtime error 13 mismatch. And it points here:
    Me.TextBox1.Text = Evaluate("SumIf(" & rngDates.Address(External:=True) & ","">=" & dStart & """," & rngSum.Address(External:=True) & ")-SumIf(" & rngDates.Address(External:=True) & ","">" & dEnd & """," & rngSum.Address(External:=True) & ")")

  22. #22
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Vba code for userform to search by range of date

    Based on the "end date not found" post, it looks like the date you're entering isn't being found within row 5 of sheet "Stocks".

    Also, I'm not sure what would cause the error you got, if nothing really changed.

  23. #23
    Forum Contributor
    Join Date
    08-24-2012
    Location
    Portugal
    MS-Off Ver
    Excel 2003
    Posts
    119

    Re: Vba code for userform to search by range of date

    Now im home from work and here i have excel 2010 which dont have this type of calendar that im using in this userform (excel 2000). That is why it gives you the couldnt find object error message.


    Edit: ok i have register the datapicker in excel 2010 from excel 2000 and put in the userform. Now everything is working ok with your last code, with the msgbox that appear is: end date not found.

    I have try changing some things in your code but i cant figure it out whats the problem, im stuck in there because i cant find why it can not find the end date when it uses it to calculate correctly the inīs and outīs.....just dont find it to display the stock at end date.
    Last edited by bazofio; 05-16-2013 at 08:43 PM.

  24. #24
    Forum Contributor
    Join Date
    08-24-2012
    Location
    Portugal
    MS-Off Ver
    Excel 2003
    Posts
    119

    Re: Vba code for userform to search by range of date

    Any ideas to solve this?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1