VBA - 在一系列工作表中循环遍历所有 ListObjects
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27750765/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
VBA - Looping through all ListObjects across a range of worksheets
提问by Joe
I have a table conditional formatting macro (thanks Jeeped) that I would like to expand to loop through all tables across a range of worksheets. I suspect this is not the most efficient approach but the its the best I could cobble together, and even then it's not working. So far I'm stuck at two points below. Any assistance is greatly appreciated!
我有一个表格条件格式宏（感谢 Jeeped），我想扩展它以遍历一系列工作表中的所有表格。我怀疑这不是最有效的方法，但它是我能拼凑起来的最好的方法，即使这样它也不起作用。到目前为止，我被困在以下两点。非常感谢任何帮助！
1) Setting ws to equal multiple worksheet codenames (e.g. Set ws = Worksheets(5,6,7))
1）将 ws 设置为等于多个工作表代号（例如Set ws = Worksheets(5,6,7)）
2) Setting the range w/o a run-time error Set myRange = ws.ListObjects.DataBodyRangeproduces "Run-time error '438': Object doesn't support this property or method"
2) 设置没有运行时错误的范围Set myRange = ws.ListObjects.DataBodyRange会产生“运行时错误‘438’：对象不支持此属性或方法”
Current code is:
当前代码是：
Sub ConditionalFormatting()
Dim ws As Excel.Worksheet
Dim lo As Excel.ListObject
Dim myRange As Range
Set ws = Worksheet(5) 'Would like to expand to include multiple worksheets!
Set myRange = ws.ListObjects.DataBodyRange
For Each lo In ws.ListObjects
With lo.FormatConditions
.FormatConditions.Delete
Call FormatRange(myRange, 10, "$E5=INDEX(Location,1,1)") 'Warehouse1
Call FormatRange(myRange, 10, "$E5=INDEX(Location,2,1)") 'Warehouse2
Call FormatRange(myRange, 10, "$E5=INDEX(Location,3,1)") 'Warehouse3
End With
Next lo
End Sub
Public Sub FormatRange(r As Range, clr As Integer, frml As String)
r.FormatConditions.Add Type:=xlExpression, Formula1:=frml
r.FormatConditions(r.FormatConditions.Count).Font.colorindex = clr
With r.FormatConditions(1).Borders(xlTop)
.LineStyle = xlContinuous
.Color = color
.TintAndShade = 0
.Weight = xlThin
End With
With r.FormatConditions(1).Borders(xlBottom)
.LineStyle = xlContinuous
.Color = color
.TintAndShade = 0
.Weight = xlThin
End With
r.FormatConditions(1).StopIfTrue = False
End Sub
回答by Tim Williams
Untested:
未经测试：
Sub ConditionalFormatting()
Dim ws As Excel.Worksheet
Dim lo As Excel.ListObject
Dim myRange As Range
Dim i
For Each i in Array(5, 6, 7)
Set ws = Worksheets(i)
For Each lo In ws.ListObjects
Set myRange = lo.DataBodyRange
myRange.FormatConditions.Delete
FormatRange myRange, 10, "$E5=INDEX(Location,1,1)" 'Warehouse1
FormatRange myRange, 10, "$E5=INDEX(Location,2,1)" 'Warehouse2
FormatRange myRange, 10, "$E5=INDEX(Location,3,1)" 'Warehouse3
Next lo
Next i
End Sub
回答by Chabu
Just like you loop over the listobject collection "Listobjects", you can loop over the Worksheet collection "WorkSheets"
就像您遍历列表对象集合“Listobjects”一样，您可以遍历工作表集合“WorkSheets”
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
'...
Next ws
