| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 '==================================== |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 '==================================== |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
alternative required; Fo Each Loop
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 '==================================== |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
alternative required; Fo Each Loop
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 '==================================== |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Test loop required | Excel Programming | |||
| Help required with 'FOR' Loop and Userforms | Excel Programming | |||
| Help required with a simple Loop | Excel Programming | |||
| Excel VBA code required (for.......next loop) | Excel Programming | |||
| For....Next loop code required | Excel Programming | |||
Hybrid Mode
