PDA

View Full Version : [SOLVED] Weird forloop behavior with worksheets



mattreingold
06-12-2018, 09:10 AM
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

mattreingold
06-12-2018, 09:12 AM
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:/

mattreingold
06-12-2018, 10:24 AM
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
...

Paul_Hossler
06-12-2018, 01:14 PM
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

mattreingold
06-13-2018, 04:49 AM
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?

Paul_Hossler
06-13-2018, 06:29 AM
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

mattreingold
06-13-2018, 09:27 AM
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 :dunno).

All part of the learning curve for me lol.

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

Aflatoon
06-14-2018, 05:41 AM
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