ExcelBanter - alternative required; Fo Each Loop

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - alternative required; Fo Each Loop (https://www.excelbanter.com/excel-programming/432514-alternative-required%3B-fo-each-loop.html)

alternative required; Fo Each Loop

I suspect that the following bit of code is causing problems - not
causing an arror - just stopping the macro part way through without an
error. Does anyone know any other ways of coding the follow, I suspect
it is the for Each loop causing the problems:

Any help appreciated
Jason

'====================================
For Each mySheet In myStorageBook.Worksheets

'check to see if the storage sheet is being used
'if it isn't then delete it
If mySheet.Range("C2") = "Empty" Then
Application.DisplayAlerts = False
mySheet.Delete
Application.DisplayAlerts = True
Else
If mySheet.Name < "Input" And mySheet.Name < "Summary"
Then
mySheet.Range("D:G").EntireColumn.AutoFit
End If
End If
Next
'====================================

alternative required; Fo Each Loop

Is this a macro or a worksheet/workbook event ?

If this post helps click Yes
---------------
Jacob Skaria

"WhytheQ" wrote:

I suspect that the following bit of code is causing problems - not
causing an arror - just stopping the macro part way through without an
error. Does anyone know any other ways of coding the follow, I suspect
it is the for Each loop causing the problems:

Any help appreciated
Jason

'====================================
For Each mySheet In myStorageBook.Worksheets

'check to see if the storage sheet is being used
'if it isn't then delete it
If mySheet.Range("C2") = "Empty" Then
Application.DisplayAlerts = False
mySheet.Delete
Application.DisplayAlerts = True
Else
If mySheet.Name < "Input" And mySheet.Name < "Summary"
Then
mySheet.Range("D:G").EntireColumn.AutoFit
End If
End If
Next
'====================================

alternative required; Fo Each Loop

On 17 Aug, 21:36, "Don Guillett" wrote:
NOT a good day
*If Len(Application.Trim(ws.range("c2"))) < 1 Then

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
"Don Guillett" wrote in message

...

I left out the most important part.....
If Len(Application.Trim(ActiveCell)) < 1 Then MsgBox "hi"
=========
For Each ws In myStorageBook.Worksheets
*If ws.Name < "Input" And ws.Name < "Summary then
*If Len(Application.Trim(ActiveCell)) < 1 Then
* * Application.DisplayAlerts = False
* * * ws.Delete
* * Application.DisplayAlerts = True
else ws.columns("D:G").AutoFit
end if
end if
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Don Guillett" wrote in message
...
I suspect this could be your problem
If mySheet.Range("C2") = "Empty" Then
Maybe assuming you have a set mystoragebook=something

For Each ws In myStorageBook.Worksheets
*If ws.Name < "Input" And ws.Name < "Summary then
* * Application.DisplayAlerts = False
* * * ws.Delete
* * Application.DisplayAlerts = True
else ws.columns("D:G").AutoFit
end if

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"WhytheQ" wrote in message
....
I suspect that the following bit of code is causing problems - not
causing an arror - just stopping the macro part way through without an
error. Does anyone know any other ways of coding the follow, I suspect
it is the for Each loop causing the problems:

Any help appreciated
Jason

'====================================
For Each mySheet In myStorageBook.Worksheets

* * * * * *'check to see if the storage sheet is being used
* * * * * *'if it isn't then delete it
* * *If mySheet.Range("C2") = "Empty" Then
* * * * * *Application.DisplayAlerts = False
* * * * * * * * *mySheet.Delete
* * * * * *Application.DisplayAlerts = True
* * *Else
* * * * * *If mySheet.Name < "Input" And mySheet.Name < "Summary"
Then
* * * * * * * * *mySheet.Range("D:G").EntireColumn.AutoFit
* * * * * *End If
* * *End If
Next
'====================================- Hide quoted text -

- Show quoted text -

Hello Don
- thanks for all the help so far.

Bit of a misunderstanding (quite understandable) - in Range("C2") of
each of the sheets there is a formula using the IF function and one of
the options is the string "Empty" ....

=IF(ISNA(MATCH(B4,Input!C6:AZ6,0)),"Empty",MATCH(B 4,Input!C6:AZ6,0))

Anyway I've moved the nested IFs around as you have and also changed
been more explicit with Range("C2") ... Range("C2").Value ; I know
this is the default property but maybe it'll help.

Will report back after testing

Thanks again
Jason.