+ Reply to Thread
Results 1 to 10 of 10

Improve code so it doesn't loop through worksheets seperately?

  1. #1
    Registered User
    Join Date
    03-07-2016
    Location
    USA
    MS-Off Ver
    ms office 2013
    Posts
    25

    Improve code so it doesn't loop through worksheets seperately?

    I have code below, works fine but notice the if statements and how it loops through two worksheets separately but ultimately does the same thing.. any ideas / advice feedback on how to make it more efficient if possible? I'm pretty new to VBA so this was the only way I new how to do this. Thanks in advance.

    Please Login or Register  to view this content.
    Last edited by carissa7; 01-17-2018 at 09:10 AM.

  2. #2
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,572

    Re: Improve code so it doesn't loop through worksheets seperately?

    code tags applied.
    Last edited by protonLeah; 01-17-2018 at 07:19 PM.
    Ben Van Johnson

  3. #3
    Registered User
    Join Date
    03-07-2016
    Location
    USA
    MS-Off Ver
    ms office 2013
    Posts
    25

    Re: Improve code so it doesn't loop through worksheets seperately?

    okay done.

  4. #4
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Improve code so it doesn't loop through worksheets seperately?

    You could do this
    Please Login or Register  to view this content.
    but note that you have not assigned a workbook to wb1.
    Don
    Please remember to mark your thread 'Solved' when appropriate.

  5. #5
    Registered User
    Join Date
    03-07-2016
    Location
    USA
    MS-Off Ver
    ms office 2013
    Posts
    25

    Re: Improve code so it doesn't loop through worksheets seperately?

    Thank you .. so I actually do have wb1 defined as the following below..

    Please Login or Register  to view this content.
    any reason as to why you created a separate sub statement?
    and now that I defined a wb1.. will this affect the code above?
    also I see process ranges in the first sub above but its not defined in that sub, how should it be defined?

  6. #6
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Improve code so it doesn't loop through worksheets seperately?

    processRanges is the name of the second routine. Since the code is the same other than a worksheet and range reference, I moved it to its own routine that you call in the first code and pass the required range. That saves you repeating all the code which, I believe, was the gist of your question.

  7. #7
    Registered User
    Join Date
    03-07-2016
    Location
    USA
    MS-Off Ver
    ms office 2013
    Posts
    25

    Re: Improve code so it doesn't loop through worksheets seperately?

    Oh I get it okay amazing, it worked thank you.. can I ask another question related to the code or do I have to post a new question?
    I want to add error handling in the else statement to tell the user if values do not copy over properly something like "ElseIf rng2.Value <> dstRng Then MsgBox rng2.Value & " not in Both sheet" however its not being recognized and I made sure to set values not equal to each other. It works perfectly when the two ranges match just not when values do not match...

    I added it in your improved code below, any ideas as to why its not working?

    Please Login or Register  to view this content.

  8. #8
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Improve code so it doesn't loop through worksheets seperately?

    I'm afraid I do not follow the logic. This line
    Please Login or Register  to view this content.
    assigns a range with the same name as the value of the rng cell.
    The If test then checks to see if that cell is part of the named range. If it isn't you want to effectively test if the content of the cell is the same as its range name?

  9. #9
    Registered User
    Join Date
    03-07-2016
    Location
    USA
    MS-Off Ver
    ms office 2013
    Posts
    25

    Re: Improve code so it doesn't loop through worksheets seperately?

    Yes I think your logic above is correct...
    I have a table with values, "A" , "B" , etc. those values sit in a column in a table in wb2, that column is called ("NamedRange")
    then I have another worksheet (ws1) in wb1 where I have set each cell with its own unique named range. Each value in ("NamedRange") wb2 should have an identical matching name in ws1. So for value "A" in ("NamedRange"), there is a cell in ws1 that has a named range called "A"
    I want to apply error handling that says if there is a value in ("NamedRange") that does not have a matching named range cell in ws1 of wb1.. then let me know what that value is with a message box. here is what I have so far...

    I added this line --> If Not dstRng Is Nothing Then
    'Check that the range exists in the appropriate area
    Else: MsgBox rng.Value & " not in RVP Local GAAP sheet"


    Please Login or Register  to view this content.
    it gives me a message box when there is a missing value however it doesn't copy over the successful ones..

  10. #10
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Improve code so it doesn't loop through worksheets seperately?

    It sounds as though you want a simple Else clause for the Is Nothing test
    Please Login or Register  to view this content.

+ 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. VBA doesn't loop through all worksheets
    By JeKoe in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-13-2017, 05:11 AM
  2. [SOLVED] Loop through all worksheets in workbook to perform action fix code
    By AWITCHER in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 01-08-2016, 02:26 PM
  3. Vba code to loop thru worksheets based on criteria
    By Godlovesmeplenty in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 06-09-2015, 12:26 PM
  4. Code to Loop through Each Worksheet in ThisWorkbook.Worksheets
    By xcelnovice101 in forum Excel Programming / VBA / Macros
    Replies: 24
    Last Post: 05-08-2014, 04:15 PM
  5. Loop code for certain worksheets in workbook
    By longbow007 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-18-2010, 01:05 AM
  6. Loop code for selected worksheets
    By longbow007 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-17-2010, 12:14 AM
  7. Need to loop code for certain worksheets in workbook
    By longbow007 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-28-2010, 08:00 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