ExcelBanter - For Each Next... what's wrong with my code???

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

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

- - For Each Next... what's wrong with my code??? (https://www.excelbanter.com/excel-programming/413567-each-next-whats-wrong-my-code.html)

For Each Next... what's wrong with my code???

Hi all,
New to this group, love what I've found so far, you've been of great help in
writing this code, but ...
Can't get it to loop thru all the sheets. Only works for the active worksheet.
Can anyone see my booboo?
Merci!
Sylvie

My Code:

Sub dataval()

Dim sh As Worksheet
Dim Rng As Range

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

For Each sh In ActiveWorkbook.Worksheets

If IsError(Application.Match(sh.Name, Array("Source", "WBS_USD",
"OLDWBS_USD", "Sales Admin Summary", _
"France Summary", "GSC Summary", "HR Summary", "Legal Summary", "Mkt
Summary", "North America Summary", "Distribution Summary", _
"Corpo Dev Summary", "Bus Dev Summary", "Administration Summary",
"R&D Summary", "vlookup"), 0)) Then

Range("A:L,N:P,R:T,V:X,Z:AB").Select

With Selection.Validation
.Delete
.Add Type:=xlValidateTextLength,
AlertStyle:=xlValidAlertStop, _
Operator:=xlEqual, Formula1:="0"
.IgnoreBlank = True
.InCellDropdown = False
.InputTitle = ""
.ErrorTitle = "Protected cells"
.InputMessage = ""
.ErrorMessage = _
"These cells are Read-Only." & Chr(10) & "Please select
CANCEL to clear this alert."
.ShowInput = True
.ShowError = True
End With
Range("A1").Select

End If

Next sh

With Application
.ScreenUpdating = True
.EnableEvents = True
End With

End Sub

For Each Next... what's wrong with my code???

Sub dataval()

Dim sh As Worksheet
Dim Rng As Range

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

For Each sh In ActiveWorkbook.Worksheets

If IsError(Application.Match(sh.Name, Array("Source", "WBS_USD",
"OLDWBS_USD", "Sales Admin Summary", _
"France Summary", "GSC Summary", "HR Summary", "Legal Summary", "Mkt
Summary", "North America Summary", "Distribution Summary", _
"Corpo Dev Summary", "Bus Dev Summary", "Administration Summary",
"R&D Summary", "vlookup"), 0)) Then

' added line
sh.select

Range("A:L,N:P,R:T,V:X,Z:AB").Select

With Selection.Validation
.Delete
.Add Type:=xlValidateTextLength,
AlertStyle:=xlValidAlertStop, _
Operator:=xlEqual, Formula1:="0"
.IgnoreBlank = True
.InCellDropdown = False
.InputTitle = ""
.ErrorTitle = "Protected cells"
.InputMessage = ""
.ErrorMessage = _
"These cells are Read-Only." & Chr(10) & "Please select
CANCEL to clear this alert."
.ShowInput = True
.ShowError = True
End With
Range("A1").Select

End If

Next sh

With Application
.ScreenUpdating = True
.EnableEvents = True
End With

End Sub

--
Regards,
Tom Ogilvy

"PixelSyl" wrote:

Hi all,
New to this group, love what I've found so far, you've been of great help in
writing this code, but ...
Can't get it to loop thru all the sheets. Only works for the active worksheet.
Can anyone see my booboo?
Merci!
Sylvie

My Code:

Sub dataval()

Dim sh As Worksheet
Dim Rng As Range

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

For Each sh In ActiveWorkbook.Worksheets

If IsError(Application.Match(sh.Name, Array("Source", "WBS_USD",
"OLDWBS_USD", "Sales Admin Summary", _
"France Summary", "GSC Summary", "HR Summary", "Legal Summary", "Mkt
Summary", "North America Summary", "Distribution Summary", _
"Corpo Dev Summary", "Bus Dev Summary", "Administration Summary",
"R&D Summary", "vlookup"), 0)) Then

Range("A:L,N:P,R:T,V:X,Z:AB").Select

With Selection.Validation
.Delete
.Add Type:=xlValidateTextLength,
AlertStyle:=xlValidAlertStop, _
Operator:=xlEqual, Formula1:="0"
.IgnoreBlank = True
.InCellDropdown = False
.InputTitle = ""
.ErrorTitle = "Protected cells"
.InputMessage = ""
.ErrorMessage = _
"These cells are Read-Only." & Chr(10) & "Please select
CANCEL to clear this alert."
.ShowInput = True
.ShowError = True
End With
Range("A1").Select

End If

Next sh

With Application
.ScreenUpdating = True
.EnableEvents = True
End With

End Sub

For Each Next... what's wrong with my code???

Looks like you're missing sheet activation before the range selection, e.g.
sh.Activate

However, selecting anything is raraly needed in Excel. Better to reference
the range directly.

With sh.Range("A:L,N:P,R:T,V:X,Z:AB").Validation

rather than

sh.Activate
ActiveSheet.Range("...").Select
Selection.Valation...

--
Tim Zych
www.higherdata.com
Compare data in worksheets and find differences with Workbook Compare
A free, powerful, flexible Excel utility

"PixelSyl" wrote in message
...
Hi all,
New to this group, love what I've found so far, you've been of great help
in
writing this code, but ...
Can't get it to loop thru all the sheets. Only works for the active
worksheet.
Can anyone see my booboo?
Merci!
Sylvie

My Code:

Sub dataval()

Dim sh As Worksheet
Dim Rng As Range

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

For Each sh In ActiveWorkbook.Worksheets

If IsError(Application.Match(sh.Name, Array("Source", "WBS_USD",
"OLDWBS_USD", "Sales Admin Summary", _
"France Summary", "GSC Summary", "HR Summary", "Legal Summary",
"Mkt
Summary", "North America Summary", "Distribution Summary", _
"Corpo Dev Summary", "Bus Dev Summary", "Administration Summary",
"R&D Summary", "vlookup"), 0)) Then

Range("A:L,N:P,R:T,V:X,Z:AB").Select

With Selection.Validation
.Delete
.Add Type:=xlValidateTextLength,
AlertStyle:=xlValidAlertStop, _
Operator:=xlEqual, Formula1:="0"
.IgnoreBlank = True
.InCellDropdown = False
.InputTitle = ""
.ErrorTitle = "Protected cells"
.InputMessage = ""
.ErrorMessage = _
"These cells are Read-Only." & Chr(10) & "Please select
CANCEL to clear this alert."
.ShowInput = True
.ShowError = True
End With
Range("A1").Select

End If

Next sh

With Application
.ScreenUpdating = True
.EnableEvents = True
End With

End Sub

For Each Next... what's wrong with my code???

Tom, you're a genius. been reading lots of your posts and you're THE Man!
Merci beaucoup have a great one !
--
"Chaos, where brilliant dreams are born"

"Tom Ogilvy" wrote:

Sub dataval()

Dim sh As Worksheet
Dim Rng As Range

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

For Each sh In ActiveWorkbook.Worksheets

If IsError(Application.Match(sh.Name, Array("Source", "WBS_USD",
"OLDWBS_USD", "Sales Admin Summary", _
"France Summary", "GSC Summary", "HR Summary", "Legal Summary", "Mkt
Summary", "North America Summary", "Distribution Summary", _
"Corpo Dev Summary", "Bus Dev Summary", "Administration Summary",
"R&D Summary", "vlookup"), 0)) Then

' added line
sh.select

Range("A:L,N:P,R:T,V:X,Z:AB").Select

With Selection.Validation
.Delete
.Add Type:=xlValidateTextLength,
AlertStyle:=xlValidAlertStop, _
Operator:=xlEqual, Formula1:="0"
.IgnoreBlank = True
.InCellDropdown = False
.InputTitle = ""
.ErrorTitle = "Protected cells"
.InputMessage = ""
.ErrorMessage = _
"These cells are Read-Only." & Chr(10) & "Please select
CANCEL to clear this alert."
.ShowInput = True
.ShowError = True
End With
Range("A1").Select

End If

Next sh

With Application
.ScreenUpdating = True
.EnableEvents = True
End With

End Sub

--
Regards,
Tom Ogilvy

"PixelSyl" wrote:

Hi all,
New to this group, love what I've found so far, you've been of great help in
writing this code, but ...
Can't get it to loop thru all the sheets. Only works for the active worksheet.
Can anyone see my booboo?
Merci!
Sylvie

My Code:

Sub dataval()

Dim sh As Worksheet
Dim Rng As Range

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

For Each sh In ActiveWorkbook.Worksheets

If IsError(Application.Match(sh.Name, Array("Source", "WBS_USD",
"OLDWBS_USD", "Sales Admin Summary", _
"France Summary", "GSC Summary", "HR Summary", "Legal Summary", "Mkt
Summary", "North America Summary", "Distribution Summary", _
"Corpo Dev Summary", "Bus Dev Summary", "Administration Summary",
"R&D Summary", "vlookup"), 0)) Then

Range("A:L,N:P,R:T,V:X,Z:AB").Select

With Selection.Validation
.Delete
.Add Type:=xlValidateTextLength,
AlertStyle:=xlValidAlertStop, _
Operator:=xlEqual, Formula1:="0"
.IgnoreBlank = True
.InCellDropdown = False
.InputTitle = ""
.ErrorTitle = "Protected cells"
.InputMessage = ""
.ErrorMessage = _
"These cells are Read-Only." & Chr(10) & "Please select
CANCEL to clear this alert."
.ShowInput = True
.ShowError = True
End With
Range("A1").Select

End If

Next sh

With Application
.ScreenUpdating = True
.EnableEvents = True
End With

End Sub

For Each Next... what's wrong with my code???

Great idea Tim, I'll try that one also.
Merci!
--
"Chaos, where brilliant dreams are born"

"Tim Zych" wrote:

Looks like you're missing sheet activation before the range selection, e.g.
sh.Activate

However, selecting anything is raraly needed in Excel. Better to reference
the range directly.

With sh.Range("A:L,N:P,R:T,V:X,Z:AB").Validation

rather than

sh.Activate
ActiveSheet.Range("...").Select
Selection.Valation...

--
Tim Zych
www.higherdata.com
Compare data in worksheets and find differences with Workbook Compare
A free, powerful, flexible Excel utility

"PixelSyl" wrote in message
...
Hi all,
New to this group, love what I've found so far, you've been of great help
in
writing this code, but ...
Can't get it to loop thru all the sheets. Only works for the active
worksheet.
Can anyone see my booboo?
Merci!
Sylvie

My Code:

Sub dataval()

Dim sh As Worksheet
Dim Rng As Range

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

For Each sh In ActiveWorkbook.Worksheets

If IsError(Application.Match(sh.Name, Array("Source", "WBS_USD",
"OLDWBS_USD", "Sales Admin Summary", _
"France Summary", "GSC Summary", "HR Summary", "Legal Summary",
"Mkt
Summary", "North America Summary", "Distribution Summary", _
"Corpo Dev Summary", "Bus Dev Summary", "Administration Summary",
"R&D Summary", "vlookup"), 0)) Then

Range("A:L,N:P,R:T,V:X,Z:AB").Select

With Selection.Validation
.Delete
.Add Type:=xlValidateTextLength,
AlertStyle:=xlValidAlertStop, _
Operator:=xlEqual, Formula1:="0"
.IgnoreBlank = True
.InCellDropdown = False
.InputTitle = ""
.ErrorTitle = "Protected cells"
.InputMessage = ""
.ErrorMessage = _
"These cells are Read-Only." & Chr(10) & "Please select
CANCEL to clear this alert."
.ShowInput = True
.ShowError = True
End With
Range("A1").Select

End If

Next sh

With Application
.ScreenUpdating = True
.EnableEvents = True
End With

End Sub