alternative required; Fo Each Loop

  • Thread starter Thread starter WhytheQ
  • Start date Start date
W

WhytheQ

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
'====================================
 
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
 
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
 
NOT a good day
 If Len(Application.Trim(ws.range("c2"))) < 1 Then

--
Don Guillett
Microsoft MVP Excel
SalesAid Software





- Show quoted text -
Click to expand...


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(B4,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.
 
Back
Top