+ Reply to Thread
Results 1 to 16 of 16

For each loop applying to all values instead of > $20.00

  1. #1
    Registered User
    Join Date
    03-13-2013
    Location
    Wichita,KS
    MS-Off Ver
    365 Aps for Enterprise
    Posts
    60

    Red face For each loop applying to all values instead of > $20.00

    Good morning (or evening) Excel/VBA wizards,

    I am trying search through all sheets in a Workbook Price List and find all cells with prices (by cell formatting - see code below) AND the cell value is greater than 20 then add a price increase and roundup to the nearest dollar. Otherwise, if the price is less than $20, add the price increase and use the Ceiling formula to round to the nearest nickel. I would like for all prices that are over $20.00 and were increased to be formatted red, if less than $20 and increased than formatted green. A colleague sent me a UserForm that they use to increase prices but it was designed to increase all values by one amount. I do not really understand how to apply an if statement to a For each loop so first, I tried modifying the Macro by adding
    Please Login or Register  to view this content.
    and thought I would run the macro twice and just change the ">20" to "<20" but even the prices that are <20 were increased and formatted red. So below on the left is what I am ultimately trying to achieve.
    2021-06-15 07_22_42-Clipboard.png. Please see below complete code and attached workbook. Thanks in advance.
    Please Login or Register  to view this content.
    Marvelous (not so much in vba!)
    Attached Files Attached Files
    Last edited by Marvelous; 06-15-2021 at 08:38 AM. Reason: Sorry - forgot to attach workbook

  2. #2
    Forum Expert dangelor's Avatar
    Join Date
    09-06-2011
    Location
    Indiana, USA
    MS-Off Ver
    365 Pro Plus
    Posts
    2,274

    Re: For each loop applying to all values instead of > $20.00

    Possibly...
    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    03-13-2013
    Location
    Wichita,KS
    MS-Off Ver
    365 Aps for Enterprise
    Posts
    60

    Re: For each loop applying to all values instead of > $20.00

    Dangelor,

    Thank you for working on this! This looks promising. When running your code, I get the popup below and when hitting debug it highlights this line:
    Please Login or Register  to view this content.
    . Maybe this is due to the cell format? The original price list excel files are exported from PDF files which give me several different number formats in the excel file. Thus the reason I had all of the "or" statements
    Please Login or Register  to view this content.
    in my original code. I know just enough VBA to be dangerous so please excuse me if I am not making any sense.Attachment 736649

  4. #4
    Forum Expert dangelor's Avatar
    Join Date
    09-06-2011
    Location
    Indiana, USA
    MS-Off Ver
    365 Pro Plus
    Posts
    2,274

    Re: For each loop applying to all values instead of > $20.00

    You posted an invalid attachment.

    What is the exact wording of the error?

  5. #5
    Registered User
    Join Date
    03-13-2013
    Location
    Wichita,KS
    MS-Off Ver
    365 Aps for Enterprise
    Posts
    60

    Re: For each loop applying to all values instead of > $20.00

    Sorry about the attachment, "Run-time error '1004': No cells were found

  6. #6
    Registered User
    Join Date
    03-13-2013
    Location
    Wichita,KS
    MS-Off Ver
    365 Aps for Enterprise
    Posts
    60

    Re: For each loop applying to all values instead of > $20.00

    Hopefully this image works Message.png

  7. #7
    Forum Expert dangelor's Avatar
    Join Date
    09-06-2011
    Location
    Indiana, USA
    MS-Off Ver
    365 Pro Plus
    Posts
    2,274

    Re: For each loop applying to all values instead of > $20.00

    The file in your first post has no problem with this code. Can you post a different workbook, one that generates this error?

  8. #8
    Registered User
    Join Date
    03-13-2013
    Location
    Wichita,KS
    MS-Off Ver
    365 Aps for Enterprise
    Posts
    60

    Re: For each loop applying to all values instead of > $20.00

    Hello Dangelor,

    Thank you for continued assistance! The actual workbook is huge with about 165 sheets. I will try to extract a couple of sheets and see if I can get the same error as I did with the entire workbook.

    Best regards

  9. #9
    Registered User
    Join Date
    03-13-2013
    Location
    Wichita,KS
    MS-Off Ver
    365 Aps for Enterprise
    Posts
    60

    Re: For each loop applying to all values instead of > $20.00

    Hello dangelor,

    I have attached a different workbook with three worksheets. I tried selecting one sheet and running macro then I tried selecting all three but in both instances I get: Runtime error 1004 no cells found. Also, I noticed that after entering the "Price Increase Round Up" that the confirmation window showed the roundup amount with two decimal points ($.0.05)as shown in this image.
    Price Increase Round Up.png. I'm not sure if that is relevant. In all cases when I click on the debug button it highlights
    Please Login or Register  to view this content.
    .

    Thanks in advance!
    Marvelous (not so much!)
    Attached Files Attached Files

  10. #10
    Forum Expert dangelor's Avatar
    Join Date
    09-06-2011
    Location
    Indiana, USA
    MS-Off Ver
    365 Pro Plus
    Posts
    2,274

    Re: For each loop applying to all values instead of > $20.00

    Sorry, but the file in post #9 will not load correctly on my system.


    Edit: Got it to load.
    Last edited by dangelor; 06-21-2021 at 08:56 AM.

  11. #11
    Registered User
    Join Date
    03-13-2013
    Location
    Wichita,KS
    MS-Off Ver
    365 Aps for Enterprise
    Posts
    60

    Re: For each loop applying to all values instead of > $20.00

    dangelor,

    This is bizarre. I saved the file as an xlsx file and I am able to download and open it without any problems. Do you have any idea why this would be happening? I have Microsoft 365 Apps for enterprise and am currently on Version 2104 (Build 13929.20408 Click-to-Run) Monthly Enterprise Channel. I have saved it as an xlsm file and re-attached it. Should I send it to you some other way?

    I know it would be easy for you to just drop this and go to other easier posts so I appreciate that you have continued to respond!

    Marvelous
    Attached Files Attached Files

  12. #12
    Forum Expert dangelor's Avatar
    Join Date
    09-06-2011
    Location
    Indiana, USA
    MS-Off Ver
    365 Pro Plus
    Posts
    2,274

    Re: For each loop applying to all values instead of > $20.00

    This seems to work on the file in Post #9
    Please Login or Register  to view this content.

  13. #13
    Registered User
    Join Date
    03-13-2013
    Location
    Wichita,KS
    MS-Off Ver
    365 Aps for Enterprise
    Posts
    60

    Re: For each loop applying to all values instead of > $20.00

    dangelor,

    Thank you so much! This works great!... if I insert the module in the actual workbook I am working on. I was trying to use my personal workbook because I have different workbooks I would like to use the code on and wasn't successful. There were no errors but it would just send me back to the vba module. I think it was going back to either the
    Please Login or Register  to view this content.
    or the
    Please Login or Register  to view this content.
    area. Is there something I need to change to place in Personal Workbook? I will mark this thread as solved as soon as I hear from you.
    Marvelous

  14. #14
    Forum Expert dangelor's Avatar
    Join Date
    09-06-2011
    Location
    Indiana, USA
    MS-Off Ver
    365 Pro Plus
    Posts
    2,274

    Re: For each loop applying to all values instead of > $20.00

    The 'Thisworkbook' refers to the workbook holding the running code, and as such, would not work on any other workbook if it was run from a Personal workbook (which actually is an open workbook with all the worksheets hidden). To make it compatible with an 'unknown to the code' open workbook, use either Activeworkbook or better, assign a variable to the active workbook, i.e., set wb = Activeworkbook.

  15. #15
    Registered User
    Join Date
    03-13-2013
    Location
    Wichita,KS
    MS-Off Ver
    365 Aps for Enterprise
    Posts
    60

    Re: For each loop applying to all values instead of > $20.00

    dangelor,

    Excellent explanation! Not only have you helped me with my project but this helps me understand the code. Best wishes
    Marvelous

  16. #16
    Forum Expert dangelor's Avatar
    Join Date
    09-06-2011
    Location
    Indiana, USA
    MS-Off Ver
    365 Pro Plus
    Posts
    2,274

    Re: For each loop applying to all values instead of > $20.00

    Good luck!

+ 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. Applying Formula to assign numeric values to text values in sequential order
    By jmshanahan in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-15-2016, 03:04 PM
  2. Replies: 2
    Last Post: 08-02-2016, 01:58 AM
  3. Applying Same Formula To Cells With Different Values
    By corvussienn in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-04-2015, 11:57 AM
  4. [SOLVED] Loop through workbook applying two different actions to two sets of sheets
    By ksayet in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-26-2015, 06:03 PM
  5. Automation: applying conditional formatting to group of cells in a loop
    By ×_× in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-21-2014, 12:11 PM
  6. Applying a formula with a for loop
    By OMG_What in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-28-2013, 06:08 PM
  7. VBA Question - Applying values
    By Scott Wagner in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 03-20-2006, 11:10 AM

Tags for this Thread

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