+ Reply to Thread
Results 1 to 16 of 16

Find function doesn't accept combination of numbers, text and values from variables

  1. #1
    Registered User
    Join Date
    02-19-2019
    Location
    Czech Republic
    MS-Off Ver
    Office 365 ProPlus, Vesrion 1812
    Posts
    9

    Find function doesn't accept combination of numbers, text and values from variables

    Hello,

    I have little script that searches for first and last day from specified month and year driven by variables. In row containing all dates from current year Find fuction should select from left to right first cell matching requested month and year and then from right to left the last matching date. I use their column index numbers to select range of cells.

    Date Cells contains date such as this: 31.01.2019 but formating is set to dd.mm.

    When I type in Find function: 01.2019 , then it works, but inside script it gives error message : Run-time error '91' Object variable or With block variable not set.

    the code follows like this

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by Onreron; 03-04-2019 at 11:53 AM.

  2. #2
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    5,057

    Re: Find function doesn't accept combination of numbers, text and values from variables

    A sample book that gives the error would be helpful. In itself I don't see an issue with the code, though cell formatting can be tricky.

    The error does suggest the problem is that the find function failed to locate the value you are searching. You are sure that the D3 and F1 are correct?

    using .Find with .Select (or activate) will always return errors if the value is not found.
    Last edited by Arkadi; 03-04-2019 at 10:33 AM.
    Please help by:

    Marking threads as closed once your issue is resolved. How? The Thread Tools at the top
    Any reputation (*) points appreciated. Not just by me, but by all those helping, so if you found someone's input useful, please take a second to click the * at the bottom left to let them know

    There are 10 kinds of people in this world... those who understand binary, and those who don't.

  3. #3
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    5,057

    Re: Find function doesn't accept combination of numbers, text and values from variables

    I would suggest using a range variable, so at least you can catch if the month/year combo was not found, which will give a message instead of an error:

    Please Login or Register  to view this content.

  4. #4
    Registered User
    Join Date
    02-19-2019
    Location
    Czech Republic
    MS-Off Ver
    Office 365 ProPlus, Vesrion 1812
    Posts
    9

    Re: Find function doesn't accept combination of numbers, text and values from variables

    Hi Arkadi, thank you for your reply, I've tried to use your code block, but it finishes with that MsgBox ("Could not locate the value.") state. I've attached a sample book in my first post, please have a look into it. I've removed other mess, that I've already putted in there, so this file should only contain relevant stuff. Cells D3 and F1 should indeed be correct, though cell F1 returns year by function =YEAR(TODAY()).

    Edit: I've realised that I didn't write the date formatting exactly. Cells contains dates like this: 31.01.2019 but formating is set to dd.mm. I've corrected in my first post as well. sory, if it made confusion.

    Might the problem be, that Find only likes input in String format ?
    Last edited by Onreron; 03-04-2019 at 11:13 AM.

  5. #5
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    5,057

    Re: Find function doesn't accept combination of numbers, text and values from variables

    Truth is I took your original code, and had no errors. That is why I wanted a copy of your file that has the error.

    To attach a sample workbook click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

  6. #6
    Registered User
    Join Date
    02-19-2019
    Location
    Czech Republic
    MS-Off Ver
    Office 365 ProPlus, Vesrion 1812
    Posts
    9

    Re: Find function doesn't accept combination of numbers, text and values from variables

    I am terribly sorry, please here find the right version of the document (I also updated it in the first post.)
    Attached Files Attached Files

  7. #7
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    5,057

    Re: Find function doesn't accept combination of numbers, text and values from variables

    Dates are messy in excel sometimes. Let's see if this works (I assume the "day" in the dates row is always the first of the month?... i mean the cell you want to find, I know the row has all days):

    Please Login or Register  to view this content.

  8. #8
    Registered User
    Join Date
    02-19-2019
    Location
    Czech Republic
    MS-Off Ver
    Office 365 ProPlus, Vesrion 1812
    Posts
    9

    Re: Find function doesn't accept combination of numbers, text and values from variables

    Yay, that worked perfectly :-) But unfortunately it solves only half of the problem. While this code block finds the first instance of the month (actually finds 1st of Month.Year) it would be tricky to use it to find the last day of the requested month.

    I created this row containg dates of the entire year simply by typing 01.01.2019 into first cell and then expanding this cell over the row with mouse using Fill series option.

    Because the last day of the month always varies (30., 31., or even 28., for February) I wanted to use the Find function starting behind the row containg all the dates of year and then search in reverse by paramater SearchDirection:=xlPrevious, so it would "hit" the first cell containing requested Month.Year, which would be the last day. Your block can't find the last day "automatically" unless user would manually specifiy the last day of requested month. Could you please add an idea, how to get column position of last day of requested month please?
    Last edited by Onreron; 03-04-2019 at 12:37 PM.

  9. #9
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    5,057

    Re: Find function doesn't accept combination of numbers, text and values from variables

    You could do something like this to select the last day of the selected month. Then add code to a button to move 1 left, or use code that will be run when a date (a cell in row 4) is selected, that will make myDate the dateserial of the activecell -1)


    Please Login or Register  to view this content.

  10. #10
    Registered User
    Join Date
    02-19-2019
    Location
    Czech Republic
    MS-Off Ver
    Office 365 ProPlus, Vesrion 1812
    Posts
    9

    Re: Find function doesn't accept combination of numbers, text and values from variables

    Oh, this looks promising. Sorry for my slowness, I'm novice and understanding unfamiliar code takes me a while.
    We can always rely on, that the requested month will be only the one defined by cell "D3" (I'm not planning this function to sum values from more than single month), so if I'm not mistaken I could ommit this entire code block ? :
    Please Login or Register  to view this content.
    And directly use my original variables, when I'll declare them as Date types? something like this ? Edit: Actually I don't need to change my original variable types, I'll leave them as Integers.
    Please Login or Register  to view this content.
    Then I'll use the dhLastDayInMonth variable in .Find function ?
    Last edited by Onreron; 03-04-2019 at 01:43 PM.

  11. #11
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    5,057

    Re: Find function doesn't accept combination of numbers, text and values from variables

    You are correct, you can omit that block of code... It is a function I have in a file that is a collection of useful tools, and did not edit the function, but as you say that part is not needed.

    The rest I left as it was because I like to try and call functions for extra bits so that the main code is easier to read, and I left the myDate in the main just in case you ever want to change the way the date is obtained. But of course you could make this much more compact if you want one simple sub. I still would, for easier reading purposes, calculate myDate and then use the myDate variable in the Find function:

    Please Login or Register  to view this content.

  12. #12
    Registered User
    Join Date
    02-19-2019
    Location
    Czech Republic
    MS-Off Ver
    Office 365 ProPlus, Vesrion 1812
    Posts
    9

    Re: Find function doesn't accept combination of numbers, text and values from variables

    Dear Arcadi, you rock! :-) It seems, that this ingenious trick (feeding DateSerial with zeroth day) beatufily does the job. It will take me while to stitch the the rest of the code together, but I feel confident, that the main obstacle is gone now.

    I found interesting thing: When I print out the dhLastDayInMonth variable:
    Please Login or Register  to view this content.
    It automatically displays it as dd/mm/yyyy. I was worried, taht the DateSerial function will return value in format mm/dd/yyyy. Or maybe it does, but it gets converted somewhere under the hood ?

  13. #13
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    5,057

    Re: Find function doesn't accept combination of numbers, text and values from variables

    because it is a date, it is displayed in the msgbox in that format, but the true value of a dateserial is a serial number representing the date.

  14. #14
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    5,057

    Re: Find function doesn't accept combination of numbers, text and values from variables

    If this topic is resolved, please mark the thread as solved? If not, then go ahead and ask more questions
    If the questions are about other part of the project, then best to start a new thread when needed.

    Best of luck, and hopefully I can help more if you need it!

  15. #15
    Registered User
    Join Date
    02-19-2019
    Location
    Czech Republic
    MS-Off Ver
    Office 365 ProPlus, Vesrion 1812
    Posts
    9

    Re: Find function doesn't accept combination of numbers, text and values from variables

    Dear Arkadi, thank you very much for your help. You've actually helped me to finish my personal workbook for time registration I'm so happy, because I had no previous experience with scripting in VBA and it took me many afternoons to put it all together and now is almost ready for real use. I'll spend a bit more time correcting and cleaning code and setting buttons to fire off actions and checking, if the macros doesn't miscalculate things etc.

    Anyway, here's the workbook, feel free to play with it and again thank you very much !
    Attached Files Attached Files

  16. #16
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    5,057

    Re: Find function doesn't accept combination of numbers, text and values from variables

    My pleasure Onreron! I'm glad I could help you in some small way to complete your project

    Thanks for marking the thread as solved, and although I don't have much time at the moment, I look forward to checking out the workbook soon!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Creating a combination of three text variables in a fourth column?
    By Elijah in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 11-16-2018, 12:25 PM
  2. IsError function doesn't accept value
    By CostCare in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-02-2017, 05:03 AM
  3. [SOLVED] Text box wont accept numbers
    By scott micklo in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-18-2016, 07:52 AM
  4. InStr function doesn't find specific text, but finds any text in column and runs code
    By mikey3580 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 03-27-2014, 03:50 AM
  5. Find function doesn't work with reference and non-visible text in cell (Excel2007)
    By Wiggert in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 12-25-2009, 09:12 AM
  6. Match Function Doesn't Find Values
    By chb20 in forum Excel General
    Replies: 2
    Last Post: 02-05-2009, 10:15 AM
  7. Histogram - Excel doesn't accept my bin values, uses default
    By GroovySpreadsheetGuy in forum Excel General
    Replies: 0
    Last Post: 11-29-2005, 01:10 PM

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