+ Reply to Thread
Results 1 to 28 of 28

If value is less than another value then...

  1. #1
    Forum Contributor
    Join Date
    10-06-2015
    Location
    Leeds, England
    MS-Off Ver
    365 Version 2202 Build 16.0.14931.21078
    Posts
    127

    If value is less than another value then...

    I'm trying to fix an issue in a workbook at work. The code takes a value from the sheet and checks if it less than another value. If it is, then it runs through some additional code. However, when the values are equal to each other, it's still running through this code. Any ideas?

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: If value is less than another value then...

    It would probably help if you shared the code and, ideally, a sample workbook.



    Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. 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 shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: If value is less than another value then...

    Might be leading or trailing spaces on one or both of the values.

  4. #4
    Forum Expert dominicb's Avatar
    Join Date
    01-25-2005
    Location
    Lancashire, England
    MS-Off Ver
    MS Office 2000, 2003, 2007 & 2016 365
    Posts
    4,867

    Re: If value is less than another value then...

    Good morning c.davidson

    You're a bit vague on information here, but … in your code, rather than using "<" for less than, try using "<=", which means less than or equal to.

    If I've misunderstood what you were after then you will need to provide more information. Like exactly what you need to do - maybe show us the code you're using as well. If it's a complicated macro, include a workbook so we can test it with your sample data.

    HTH

    DominicB
    Please familiarise yourself with the rules before posting. You can find them here.

  5. #5
    Forum Contributor
    Join Date
    10-06-2015
    Location
    Leeds, England
    MS-Off Ver
    365 Version 2202 Build 16.0.14931.21078
    Posts
    127

    Re: If value is less than another value then...

    I tried to copy and paste the code in my post but it wasn't working, and there was no option to place the code in the text style for displaying code :/

    The code is:
    Please Login or Register  to view this content.
    I have checked the values by stepping through the code and they both appear as values 48.1 and 48.1 respectively but it's reading it as one is less than the other. I've tried to replicate this in a new workbook bit it seems to be working. So there must be an issue with my original workbook which I can't share unfortunately.
    Last edited by AliGW; 04-18-2019 at 04:24 AM.

  6. #6
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: If value is less than another value then...

    The code looks ok. I take it that Value1 and Value2 are Named Ranges? Have you just retyped the code rather than copying and pasting it?

    Without the data, I can't suggest anything else.

  7. #7
    Forum Contributor
    Join Date
    10-06-2015
    Location
    Leeds, England
    MS-Off Ver
    365 Version 2202 Build 16.0.14931.21078
    Posts
    127

    Re: If value is less than another value then...

    That's correct, and yes I've had to retype the code unfortunately.

    Like I say trying to replicate in a new workbook doesn't have any issues so it must be something wrong with the original workbook that I'll have to investigate further. Thanks anyway.

  8. #8
    Forum Expert dominicb's Avatar
    Join Date
    01-25-2005
    Location
    Lancashire, England
    MS-Off Ver
    MS Office 2000, 2003, 2007 & 2016 365
    Posts
    4,867

    Re: If value is less than another value then...

    Hi c.davidson

    No-one is asking you to share the actual workbook - a mocked up version with just a few lines of data that demonstrates the issue will suffice. From our point of view, there could be multiple issues here causing your problem and from the information you've posted so far it will be pretty difficult to determine without seeing more.


    HTH

    DominicB


    PS I've added your code tags - please try and remember next time.
    More information about these and other tags can be found here

  9. #9
    Forum Contributor
    Join Date
    10-06-2015
    Location
    Leeds, England
    MS-Off Ver
    365 Version 2202 Build 16.0.14931.21078
    Posts
    127

    Re: If value is less than another value then...

    Thank you, I was looking for how to apply code tags. I was used to seeing it as an option when typing a post. And I've tried mocking up a version but it doesn't experience the issue I'm seeing so it would be pointless to share.

  10. #10
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,404

    Re: If value is less than another value then...

    Have you checked that the values are really equal to each other and that you are not trying to compare apples and pears (e.g. text and a numeric value)? Are there trailing or leading spaces in one of the entries that are causing a mismatch?
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  11. #11
    Forum Contributor
    Join Date
    10-06-2015
    Location
    Leeds, England
    MS-Off Ver
    365 Version 2202 Build 16.0.14931.21078
    Posts
    127

    Re: If value is less than another value then...

    I thought about this and even included multiplying the values by 1 (*1) to ensure it was seeing it as a number, however it still saw that one value was less than the other. I can see no spaces.

  12. #12
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,404

    Re: If value is less than another value then...

    Ah, then you probably have a floating point issue. Increase the number of decimal places on each until you find the mismatch.

    If it is this, then INT(cell_address) on each SHOULD return the same value and therefore a match.

  13. #13
    Forum Contributor
    Join Date
    10-06-2015
    Location
    Leeds, England
    MS-Off Ver
    365 Version 2202 Build 16.0.14931.21078
    Posts
    127

    Re: If value is less than another value then...

    Thank you for the suggestion, but I need it to work to one decimal place. The value it is checking it against (Value2) is using the roundup function to 1 decimal place, resulting in 48.1. Then if I type 48.1 as Value1 is it is seeing 48.1 < 48.1 for some reason.

  14. #14
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,404

    Re: If value is less than another value then...

    Yes, I realise this. You are missing the point (no pun intended!): the mismatch is lurking there beyond your one decimal place. Unless you actually round your numbers, they could still be different (e.g. 48.1000000000000000000000000000001 and 48.1000000000000000000000000000002). Your one decimal place is only what you can SEE, not the value that is actually there.

    Try this on each and see if they match then:

    =ROUND(cell_address,1)

  15. #15
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,404

    Re: If value is less than another value then...

    Have you tested the values in the cells that don't match yet? I've explained how to do this.

  16. #16
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: If value is less than another value then...

    Can you extract the specific sheet and redact any sensitive data and share that, assuming it still demonstrates the problem?

  17. #17
    Forum Contributor
    Join Date
    10-06-2015
    Location
    Leeds, England
    MS-Off Ver
    365 Version 2202 Build 16.0.14931.21078
    Posts
    127

    Re: If value is less than another value then...

    I can't extract the information from the workbook unfortunately. It's so large with so much VBA code and other dependencies that it would just take too long.

    I tried expanding the decimal point as far as I could but it just showed 48.1000000.....

    I've even used just a formula IF statement to check if Value1 is less than Value2 which resulted in FALSE, so it's odd that VBA is seeing the opposite.

  18. #18
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,404

    Re: If value is less than another value then...

    Can you just copy and paste the two mismatching cells into a new workbook and post that here? Just bog standard copy and paste - nothing special.

    I've even used just a formula IF statement to check if Value1 is less than Value2 which resulted in FALSE
    This means that the formula is seeing them as equal, otherwise it would return TRUE. Mmmmm ...

    What does this return?

    =ROUND(Value1,1)=ROUND(Value2,1)
    Last edited by AliGW; 04-18-2019 at 05:26 AM.

  19. #19
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: If value is less than another value then...

    All we need is the sheet with the Named Ranges and values. Nothing else. We already have the code to test.

    Are the values numeric constants or the result of formulae? Maybe one formula produces text and the other a number. Using LEFT, MID, RIGHT, CONCATENATE, etc., produces text values.

  20. #20
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: If value is less than another value then...

    OK, in the absence of anything to work with, I’m going to have to declare myself out. Good luck in diagnosing the problem. Please let us know if you find a solution.

    @Ali: thanks for the feedback.

  21. #21
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,404

    Re: If value is less than another value then...

    C.Davison - by failing to provide the simple data we have asked for and by failing to conduct the simple tests we've asked you to conduct (and, instead, conducting your own), you are making it very difficult, nigh on impossible, in fact, for us to help you. I, too, am going to declare myself out UNLESS you provide the following within the next few minutes:

    1. The sheet with the Named Ranges and values. Nothing else. We already have the code to test. (as requested by TMS)
    2. The result of this test: =ROUND(Value1,1)=ROUND(Value2,1) (as requested by me)

    This really is the very least you can do in return for the effort made on your behalf already. I do not understand why you are finding it so difficult to provide this simple data and information.

  22. #22
    Forum Contributor
    Join Date
    10-06-2015
    Location
    Leeds, England
    MS-Off Ver
    365 Version 2202 Build 16.0.14931.21078
    Posts
    127

    Re: If value is less than another value then...

    For starters... I'm at WORK, so I have other responsibilities at hand to attend to than spending all my time on this forum. I'm trying to provide as much information as possible without releasing confidential information from the worksheet. It isn't as simple as just cutting a section out into a new workbook.

    I've tried to replicate the issue in a mock workbook and it isn't giving the same results, so it would be helpful to nobody for me to share this.

    The result of the test you asked for is TRUE, rounding to 1 decimal place of each value does equal each other. However vba still not see this.

    I will continue to work on this on my own when I can find time, I'm sorry I couldn't meet your demands.

  23. #23
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,404

    Re: If value is less than another value then...

    OK - we were not to know that you are at work - you never said this until now, and SHOUTING it at us as if we were meant to know isn't going to help.

    The fact is that we CANNOT troubleshoot it any further without seeing something in context. If you cannot provide this, for whatever reason, there is nothing more we can do to help. Sorry.

  24. #24
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: If value is less than another value then...

    I'm trying to fix an issue in a workbook at work.
    That could imply that you are at work, or that you have a problem with a workbook you use at work ... but you are diagnosing the issue at home. Who knows? Whatever, it's not important, at least not to us. A lot of people on the forum are at work but take time out to provide help on the forum ... they volunteer their time.

    Wherever you are, and whatever you are doing, you came to the forum to ask for help so, I’m guessing it was ok to ask the question, but not ok to provide any data to help us help you ... you didn't even give us the code without being asked. You've got a fair few posts so you must know how this works. You help us, we help you.

  25. #25
    Forum Contributor
    Join Date
    10-06-2015
    Location
    Leeds, England
    MS-Off Ver
    365 Version 2202 Build 16.0.14931.21078
    Posts
    127

    Re: If value is less than another value then...

    I do appreciate the help and I did try to post the code initially. As I said, it would not allow me to copy and paste, and I'm familiar with the button being there when writing a post to enclose the code, but it wasn't there.

    I was hoping that it would be a simple fix without needing the data, but if it's necessary then there's just simply too much work involved to extract it out of the workbook when I have other things to do today.

  26. #26
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: If value is less than another value then...

    Well, you've already come to the conclusion that it is something to do with the original workbook, so that narrows it down. But, you would need to share at least part of it for any of us to help.

    Good luck. Come back if and when you have some time to pursue this. Given that you're up to post #25, I would suggest that you mark this as solved (although it clearly isn't) and start a new thread when you are in the position to provide more information. That will attract new eyes and more interest than continuing to flog this poor old horse.

  27. #27
    Forum Contributor
    Join Date
    10-06-2015
    Location
    Leeds, England
    MS-Off Ver
    365 Version 2202 Build 16.0.14931.21078
    Posts
    127

    Re: If value is less than another value then...

    I do wish I could share the workbook, as I can't replicate it in a new one, but it does contain confidential information and is a large workbook so it would take a lot of time to modify it to post.
    I've found a quick workaround for now by just multiplying the figure by 1.00000000001.

  28. #28
    Valued Forum Contributor
    Join Date
    03-24-2014
    Location
    England
    MS-Off Ver
    Excel 2003 - 2016
    Posts
    575

    Re: If value is less than another value then...

    Try this:

    Please Login or Register  to view this content.

    The VAL will convert whatever data type the cell value is to a numerical value. The INT and *10 bit will ensure you're dealing with a number that only has 1 decimal place, even if the number in the cell is actually 48.1000000000000001.

+ 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