我如何参考工作表循环使用不同的工作表,并对每个工作表执行相同的功能? [英] How do I refer to worksheets to loop through different worksheets and perform the same function on each of them?
问题描述
我在For循环中遇到问题,该循环应该通过定义的sh1循环到sh3。如果使用表单,我如何声明表单循环(sh& i)不适用于For循环?
Sheets(sh& i).Activate
part是我收到调试错误的地方。
运行时错误:9
下标超出范围
我在很大程度上不确定我在做什么,并尝试了很多。在这方面,是否有任何好的在线学习课程可以用来增加我对VBA的了解?
函数updateWWColumn )
设置sh1 = ActiveWorkbook.Sheets(Data_WE)
设置sh2 = ActiveWorkbook.Sheets(Data_E)
设置sh3 = ActiveWorkbook.Sheets(Data_PC)
'占位符从右侧的单元格中以整数形式包含周数。
Dim placeholder As Integer
Dim i As Integer
For i = 1 To 3
Sheets(sh& i).Activate'运行时错误: 9个下标超出范围
ActiveSheet.Columns(U:U)。插入Shift:= xlToRight,CopyOrigin:= xlFormatFromLeftOrAbove
工作表(sh& i).Columns(P:P ).Delete Shift:= xlToLeft
placeholder = Right(Worksheets(sh& i).Range(S83),2)
工作表(sh& i) (T83)Value =WW&占位符+ 1
下一个i
结束函数
不要使用三个变量来存储表单引用 - 使用存储特定表单的三个数组。
所以我所有的这里做的是将sh1,sh2和sh3改成一个名为sh的数组。然后,无论何处使用 WorkSheets(sh& i)
,您只需使用 sh(i)
函数updateWWColumn()
Dim sh(3)As Worksheet
Set sh(1)= ActiveWorkbook.Sheets (Data_WE)
设置sh(2)= ActiveWorkbook.Sheets(Data_E)
设置sh(3)= ActiveWorkbook.Sheets(Data_PC)
'占位符周号从整数形式从右边的单元格。
Dim占位符作为Interger
Dim i As Integer
对于i = 1至3
sh(i).Activate
ActiveSheet.Columns(U :U)。 _
插入Shift:= xlToRight,_
CopyOrigin:= xlFormatFromLeftOrAbove
sh(i).Columns(P:P)。 _
删除Shift:= xlToLeft
placeholder = Right(sh(i).Range(S83),2)
sh(i).Range(T83 ).Value =WW&占位符+ 1
下一个i
结束函数
I'm new to VBA and am trying to learn it. I've written this function to try to loop through some worksheets within the same workbook and add a column to update the week notated as 'ww' based on the cell to the left.
I'm having issues with the For loop where it's supposed to loop through the defined sh1 to sh3. How do I declare the sheets to loop if using sheet.("sh"&i) does not work for the For loop?
The Sheets("sh" & i).Activate
part is where I receive the debug error.
"Run time error: 9
subscript out of range"
I'm largely unsure of what I'm doing and experimenting a lot. On the side not, is there any good online learning courses that I can use to grow my knowledge on VBA?
Function updateWWColumn()
Set sh1 = ActiveWorkbook.Sheets("Data_WE")
Set sh2 = ActiveWorkbook.Sheets("Data_E")
Set sh3 = ActiveWorkbook.Sheets("Data_PC")
'placeholder to contain the week number in integer form from the cell on the right.
Dim placeholder As Integer
Dim i As Integer
For i = 1 To 3
Sheets("sh" & i).Activate ' Run time error: 9 subscript out of range
ActiveSheet.Columns("U:U").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Worksheets("sh" & i).Columns("P:P").Delete Shift:=xlToLeft
placeholder = Right(Worksheets("sh" & i).Range("S83"), 2)
Worksheets("sh" & i).Range("T83").Value = "WW" & placeholder + 1
Next i
End Function
Don't use three variables to store your sheet references - use an array of three items that store your specific sheets.
So all I have done here is change sh1, sh2 and sh3 into an array called sh. Then wherever you would useWorkSheets("sh"&i)
you can just use sh(i)
Function updateWWColumn()
Dim sh(3) As Worksheet
Set sh(1) = ActiveWorkbook.Sheets("Data_WE")
Set sh(2) = ActiveWorkbook.Sheets("Data_E")
Set sh(3) = ActiveWorkbook.Sheets("Data_PC")
'placeholder to contain the week number in integer form from the cell on the right.
Dim placeholder As Interger
Dim i As Integer
For i = 1 To 3
sh(i).Activate
ActiveSheet.Columns("U:U"). _
Insert Shift:=xlToRight, _
CopyOrigin:=xlFormatFromLeftOrAbove
sh(i).Columns("P:P"). _
Delete Shift:=xlToLeft
placeholder = Right(sh(i).Range("S83"), 2)
sh(i).Range("T83").Value = "WW" & placeholder + 1
Next i
End Function
这篇关于我如何参考工作表循环使用不同的工作表,并对每个工作表执行相同的功能?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!