+ Reply to Thread
Results 1 to 18 of 18

Need to Create a Do While Loop That Looks Changes Values Across a Row

  1. #1
    Registered User
    Join Date
    01-17-2018
    Location
    Buffalo, New York
    MS-Off Ver
    2016
    Posts
    24

    Need to Create a Do While Loop That Looks Changes Values Across a Row

    Hello!

    I am trying to create a sub that creates an inputbox and then takes the information from this and searches through a column for that value. If the value is found then I would like to change a value of a cell in the same row.

    For example, if you enter the value of 1000 into the inputbox then go search B1:B1500 for that value. If you find that in B250 then go change the value of H250 to "500-00-01". I know I can use the Do While loop (although For Next might work as well) but I can't figure out exactly how to shift over the cell. Here is what I ha


    Please Login or Register  to view this content.
    Thanks in advance everyone!

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Need to Create a Do While Loop That Looks Changes Values Across a Row

    Looping should be the last resort since most of the time it just slows things down.

    You should use some simple FIND syntax to find the value you're looking for and the row number that contains it then use the row number and update the column H cell with the same row number accordingly.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    5,497

    Re: Need to Create a Do While Loop That Looks Changes Values Across a Row

    Would this be a start?
    Please Login or Register  to view this content.

  4. #4
    Registered User
    Join Date
    01-17-2018
    Location
    Buffalo, New York
    MS-Off Ver
    2016
    Posts
    24

    Re: Need to Create a Do While Loop That Looks Changes Values Across a Row

    Quote Originally Posted by jolivanes View Post
    Would this be a start?
    Please Login or Register  to view this content.
    That seems like a good start but I got a type mismatch on that range.

    Is something wrong with this?
    Please Login or Register  to view this content.

  5. #5
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    5,497

    Re: Need to Create a Do While Loop That Looks Changes Values Across a Row

    Did you get an error message before you changed it?
    Show all of your code.

  6. #6
    Registered User
    Join Date
    01-17-2018
    Location
    Buffalo, New York
    MS-Off Ver
    2016
    Posts
    24

    Re: Need to Create a Do While Loop That Looks Changes Values Across a Row

    Quote Originally Posted by jolivanes View Post
    Did you get an error message before you changed it?
    Show all of your code.
    Sorry about that! See below.



    When I run this code:
    Please Login or Register  to view this content.
    I get an error message "Object variable or With block variable not set". Should we have to set it?

    When I run this code:
    Please Login or Register  to view this content.
    I get a 'type mismatch' error.

  7. #7
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    5,497

    Re: Need to Create a Do While Loop That Looks Changes Values Across a Row

    Can you attach a sanitized workbook.

  8. #8
    Registered User
    Join Date
    01-17-2018
    Location
    Buffalo, New York
    MS-Off Ver
    2016
    Posts
    24

    Re: Need to Create a Do While Loop That Looks Changes Values Across a Row

    Quote Originally Posted by jolivanes View Post
    Can you attach a sanitized workbook.
    Sorry I am not familiar with the term sanitized in this context. I did some research and I think you are talking about removing the user data entry component. I think this is a necessary component to my goal here.

    Or maybe you mean to remove the unique names of the code? Sorry, can you clarify a bit, I'm new here.

  9. #9
    Registered User
    Join Date
    01-17-2018
    Location
    Buffalo, New York
    MS-Off Ver
    2016
    Posts
    24

    Re: Need to Create a Do While Loop That Looks Changes Values Across a Row

    I have attached a version of the workbook that has removed any confidential type data. See attached!
    Attached Files Attached Files

  10. #10
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Need to Create a Do While Loop That Looks Changes Values Across a Row

    Sanitising in this context means replacing any names, information..etc. that is personal or private and would fall foul of any data protection legislation, or indeed commercially sensitble information with dummy or random data.

    i.e. change names to 'Mickey Mouse", "Donald Duck",....."Donald Trump"...whatever, but I guess you get the point. Second thoughts maybe not the latter. :-)

  11. #11
    Registered User
    Join Date
    01-17-2018
    Location
    Buffalo, New York
    MS-Off Ver
    2016
    Posts
    24

    Re: Need to Create a Do While Loop That Looks Changes Values Across a Row

    Quote Originally Posted by Richard Buttrey View Post
    Sanitising in this context means replacing any names, information..etc. that is personal or private and would fall foul of any data protection legislation, or indeed commercially sensitble information with dummy or random data.

    i.e. change names to 'Mickey Mouse", "Donald Duck",....."Donald Trump"...whatever, but I guess you get the point. Second thoughts maybe not the latter. :-)
    Ah, got it. Thank you. I have done most of that in the attached workbook above.

  12. #12
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    5,497

    Re: Need to Create a Do While Loop That Looks Changes Values Across a Row

    Is your original the same as your attached? 27 of the same invoice numbers?

  13. #13
    Registered User
    Join Date
    01-17-2018
    Location
    Buffalo, New York
    MS-Off Ver
    2016
    Posts
    24

    Re: Need to Create a Do While Loop That Looks Changes Values Across a Row

    Quote Originally Posted by jolivanes View Post
    Is your original the same as your attached? 27 of the same invoice numbers?
    Yes, each line represents an item on an invoice. If an invoice has 27 items then 27 invoice numbers will generate. Later on in the process it combines things. This is why I thought I would need to do a DO While loop, there are many cells to search.

    Basically, if a value that we enter into our msgbox equals an item in column B then the value needs to change in the same row of column H.

  14. #14
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    5,497

    Re: Need to Create a Do While Loop That Looks Changes Values Across a Row

    Please Login or Register  to view this content.
    See how fast or slow this is.
    Change references as required of course.

  15. #15
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    5,497

    Re: Need to Create a Do While Loop That Looks Changes Values Across a Row

    If looping is too slow, you could try this.
    Please Login or Register  to view this content.

  16. #16
    Registered User
    Join Date
    01-17-2018
    Location
    Buffalo, New York
    MS-Off Ver
    2016
    Posts
    24

    Re: Need to Create a Do While Loop That Looks Changes Values Across a Row

    Quote Originally Posted by jolivanes View Post
    Please Login or Register  to view this content.
    See how fast or slow this is.
    Change references as required of course.
    It worked! Wow, thank you for sticking with me through that. I do have a quick question...
    This code:
    Please Login or Register  to view this content.
    Is this basically saying B4 through anywhere where there is content? I am a bit confused as to what that it is doing.

  17. #17
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Need to Create a Do While Loop That Looks Changes Values Across a Row

    As I mentioned in #2 I always try to avoid loops since with larger ranges they can make things exceedingly slow. My preference is to always filter your data to present the cells you want to work with and then make a single block change with thexlCellTypeVisible syntax.
    Your data isnt that large so it doesn't really matter here, but the way I'd do it would be.

    Please Login or Register  to view this content.

  18. #18
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    5,497

    Re: Need to Create a Do While Loop That Looks Changes Values Across a Row

    Re:
    Please Login or Register  to view this content.
    All it says is all the cells from cell B4 to the last used cell in column B.
    If the loop is slow, you can try the two suggestions with autofilter.
    Good luck and thanks for letting us know.

+ 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. Create list of non-contiguous cells and loop back to overwrite values
    By mechvba in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-27-2017, 05:07 PM
  2. Loop VBA code to create a pricelist based on input values in "Calculator" sheet
    By chevwayne1509 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 04-13-2016, 04:49 AM
  3. create a for loop within a for loop
    By ammartino44 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 06-02-2015, 02:28 PM
  4. [SOLVED] I want to create loop with a loop
    By wolfdemon710 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 05-28-2015, 03:33 PM
  5. [SOLVED] Macro to create multiple sheet, copy certain values and loop until end of row data
    By jhoelski in forum Excel Programming / VBA / Macros
    Replies: 18
    Last Post: 02-17-2012, 07:35 AM
  6. create a loop that adds values
    By short_n_curly in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-14-2009, 12:43 PM
  7. How do I create a For loop within a For loop?
    By Linking to specific cells in p in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-24-2005, 06:07 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