Consulting

Results 1 to 8 of 8

Thread: Weird forloop behavior with worksheets

  1. #1

    Weird forloop behavior with worksheets

    Hello everyone, thank you in advance for your help.

    The weirdest thing is happening to me while trying to code this macro, I am trying to run a forloop to delete worksheets 'numberOfFiles' to 17.

    'n...' is a variable which populates from user input. Essentially, the first sheet in this workbook is a collective table of all samples, sheet 2 is a cumulative chart of all samples (stress strain graphs), then sheet 3 is specific information on sample 1, sheet 4 is specific information on sample 2 and so on.

    If there is only one sample, then, I want to delete sheets 3 thru 17, if there are 2 samples - 4 thru 17 and so on.

    The problem is, when I run this forloop - it deletes all even sheets starting at Sheet4, not every sheet starting at Sheet4.

    This, then, throws a subscript out of bounds error. Am I just missing something so simple here in my forloop indexing?

    P.S. The second forloop deleting legend entries works fine.

    Dim WBT As Workbook ' This Workbook
    numberOfFiles = InputBox("Enter Number of Samples to Analyze")
    Set WBT = Workbooks("RunCompressionTestingReport.xlsm")
    
    ...
    Else If numberOfFiles = 2 Then
         With WBT
               For ytu = numberOfFiles + 3 To 17
                    Sheets(ytu).Delete
               Next ytu
         End With
         With TotalCht.Chart
               For emg = 15 To numberOfFiles + 1
                    .Legend.LegendEntries(emg).Delete
               Next emg
         End With
    Else If numberOfFiles = 1 Then
         With WBT
               For ytu = numberOfFiles + 3 To 17
                    Sheets(ytu).Delete
               Next ytu
         End With
         With TotalCht.Chart
               For emg = 15 To numberOfFiles + 1
                    .Legend.LegendEntries(emg).Delete
               Next emg
         End With
    End If

  2. #2
    I actually just modified my code to:

    Dim WBT As Workbook ' This Workbook
    numberOfFiles = InputBox("Enter Number of Samples to Analyze")
    Set WBT = Workbooks("RunCompressionTestingReport.xlsm")
    
    ...
    Else If numberOfFiles = 2 Then
         With WBT
               For ytu = numberOfFiles + 3 To 17
                    .worksheets(ytu).Delete
               Next ytu
         End With
         With TotalCht.Chart
               For emg = 15 To numberOfFiles + 1
                    .Legend.LegendEntries(emg).Delete
               Next emg
         End With
    Else If numberOfFiles = 1 Then
         With WBT
               For ytu = numberOfFiles + 3 To 17
                    .worksheets(ytu).Delete
               Next ytu
         End With
         With TotalCht.Chart
               For emg = 15 To numberOfFiles + 1
                    .Legend.LegendEntries(emg).Delete
               Next emg
         End With
    End If
    Using .worksheets instead of Sheets and now nothing deleted at all...

    Very confused:/

  3. #3
    I understand! When deleting sheets, after deleting "Sheet4", "Sheet5" becomes the fourth sheet, thus this code works:

               ElseIf numberOfFiles = 2 Then
                    With WBT
                          For ytu = 1 To 13
                               Sheets(5).Delete
                          Next ytu
                    End With
                    With Chart1
                          For emg = 1 To 13
                               .SeriesCollection(3).Delete
                          Next emg
                    End With
                ElseIf numberOfFiles = 1 Then
                    With WBT
                          For ytu = 1 To 14
                               Sheets(4).Delete
                          Next ytu
                    End With
                    With Chart1
                          For emg = 1 To 14
                               .SeriesCollection(2).Delete
                          Next emg
                    End With
                Else
    ...

  4. #4
    VBAX Wizard
    Joined
    Apr 2007
    Posts
    5,705
    Location
    IMHO it's very risky to rely a worksheet's index since as you found they can change

    It'd be better to determine a 'signature' on the sheets to delete and use that, or something else
    Paul

    ------------------------------------------------------------------------------------------------------------------------
    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s)
    (multiple files can be selected while holding Ctrl key) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  5. #5
    Paul, I was originally just hardcoding it super badly with WBT.Worksheets("Sheet3").Delete and repeating the statement over 15 some lines for each iteration, I was just looking for a better way of doing so. This code is pretty consistent, it acts as a seed file so using the index shouldn't cause any problems necessarily, however, how would you suggest deleting them using this signature thing you mentioned?

  6. #6
    VBAX Wizard
    Joined
    Apr 2007
    Posts
    5,705
    Location
    I just meant that (for example)

    1. if A1 contains "LOCATION" and B1 contains "DATE", then the macro can loop and check A1 and B1 to decide if it can be deleted, or

    2. if the WS names start with a consistent string or follow a pattern ("Month01", "Month02", …) -- same thing
    Paul

    ------------------------------------------------------------------------------------------------------------------------
    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s)
    (multiple files can be selected while holding Ctrl key) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  7. #7
    Ah, I see.

    I would have done something like that, however the way this macro works is it is a shell for data input. It can analyze up to 30 samples (has a sheet for each) and if someone only imports 5, I want it to delete the 25 following sheets.

    I know this is probably the worst way to do this (adding sheets and formatting accordingly would probably be 100x more effective) however for the scope of this project and repetitious nature, it will suffice.

    Also, the format of each sheet is highly complicated (in hindsight, probably should have made one sheet fully formatted and copied to a new one for each sample ).

    All part of the learning curve for me lol.

    Thank you for your guidance, though - I really appreciate your time.

  8. #8
    VBAX Master Aflatoon's Avatar
    Joined
    Sep 2009
    Location
    Not far
    Posts
    1,526
    Also, as a general comment, when deleting it's better to work backwards (for the reason you encountered):

    For ytu = 17 to numberOfFiles + 3 Step -1
    Be as you wish to seem

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •