+ Reply to Thread
Results 1 to 6 of 6

Loop through a DIM variable

  1. #1
    Registered User
    Join Date
    02-04-2018
    Location
    Canberra, Australia
    MS-Off Ver
    2013
    Posts
    74

    Loop through a DIM variable

    Hi All,

    I need to loop through a series of worksheets and I have the worksheets DIMed As String so I can reference them.

    I can't get the loop to recognise the number of the assigned Worksheet.

    Please Login or Register  to view this content.
    How do I get ws to equal the i?

    Any help appreciated

    Cheers

    Dean

  2. #2
    Valued Forum Contributor
    Join Date
    05-03-2022
    Location
    Halifax,Canada
    MS-Off Ver
    365
    Posts
    326

    Re: Loop through a DIM variable

    This will reference every sheet in the workbook that contains the code .

    Please Login or Register  to view this content.

    this code will loop thru specific named sheets
    Please Login or Register  to view this content.
    HERE'S EXCELLENT REFERENCE ON SUBJECT : https://docs.microsoft.com/en-us/off...lls-and-ranges

    if this code has helped please consider clicking "add reputation" , thx
    Last edited by nimrod1313; 08-31-2022 at 04:13 AM.

  3. #3
    Forum Expert CheeseSandwich's Avatar
    Join Date
    12-22-2021
    Location
    Kent, England
    MS-Off Ver
    365 - 2402-17328.20068
    Posts
    1,359

    Re: Loop through a DIM variable

    If you want to loop through specific sheets then you could use something like the below:
    Please Login or Register  to view this content.
    Also no need for select, see how tha above can write to each sheet without selecting.
    If things don't change they stay the same

  4. #4
    Valued Forum Contributor
    Join Date
    05-03-2022
    Location
    Halifax,Canada
    MS-Off Ver
    365
    Posts
    326

    Re: Loop through a DIM variable

    This solution loops thru each sheet name AND checks that the sheet exists before accessing it.
    If the sheet does not exist the macro tells you so and exits the process.
    This example uses a re-usable function to perform a "Safe Set" (ie. safeSetSheet)
    In MY ACTUAL CODE I almost always use the "Safe Set" method
    Without a "Safe Set" method your macro will crash if sheet does not exist

    Please Login or Register  to view this content.
    HERE'S EXCELLENT REFERENCE ON SUBJECT : https://docs.microsoft.com/en-us/off...lls-and-ranges

    if this code has helped please consider clicking "add reputation" , thx
    Last edited by nimrod1313; 08-31-2022 at 04:16 AM.

  5. #5
    Valued Forum Contributor
    Join Date
    05-03-2022
    Location
    Halifax,Canada
    MS-Off Ver
    365
    Posts
    326

    Re: Loop through a DIM variable

    Accessing many sheets at once
    This example uses a re-usable function to perform a "Safe Set" (ie. safeSetSheet)
    In MY ACTUAL CODE I almost always use the "Safe Set" method
    Without a "Safe Set" method your macro will crash if sheet does not exist

    WITH SAFESET
    Please Login or Register  to view this content.

    WITHOUT SAFESET
    NOTE: Without a "Safe Set" method your macro will crash if sheet does not exist
    Please Login or Register  to view this content.
    HERE'S EXCELLENT REFERENCE ON SUBJECT : https://docs.microsoft.com/en-us/off...lls-and-ranges

    if this code has helped please consider clicking "add reputation" , thx
    Last edited by nimrod1313; 08-31-2022 at 04:18 AM.

  6. #6
    Valued Forum Contributor
    Join Date
    05-03-2022
    Location
    Halifax,Canada
    MS-Off Ver
    365
    Posts
    326

    Re: Loop through a DIM variable

    Four demos on referencing the same sheet multiple times ( from worst to best)

    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    note the practice of giving the sheet Var a meaningful name that helps indicate intent (eg. its the target sheet )

    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    if this code has helped please consider clicking "add reputation" , thx
    Last edited by nimrod1313; 08-31-2022 at 12:48 PM.

+ 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] Error handling not working in "vba object variable or with block variable not set" in Loop
    By caabdul in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-23-2019, 06:40 PM
  2. [SOLVED] Can i make the column I want to loop a variable? (i,VARIABLE) instead of (i,2)
    By trenzalore888 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-31-2017, 12:48 PM
  3. [SOLVED] correctly 'wording' a for/next loop using a variable counter and variable filter
    By DBoyd121 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 07-14-2016, 06:13 PM
  4. [SOLVED] VB Loop different variables and stop variable to find a new variable..
    By feroguz in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-14-2014, 04:54 PM
  5. Replies: 6
    Last Post: 12-21-2012, 08:03 AM
  6. Why did an inner loop variable start overwriting the outer loop range suddenly?
    By 111StepsAhead in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-16-2012, 03:24 PM
  7. [SOLVED] For Each ... Next loop - need to reference the loop variable
    By [email protected] in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-13-2006, 01:15 PM

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