对于i = 2到thisworkbook.worksheets.count通过工作簿中的所有工作表
问题描述:
任何人都可以帮我吗?我试图让我的代码循环遍历工作簿中的所有工作表。它完美适用于一个工作表,但我努力使其循环遍历所有工作表对于i = 2到thisworkbook.worksheets.count通过工作簿中的所有工作表
Sub stringcheck()
Dim MainString As String
Dim SubString As String
Dim Lastrow As Long, Lcount As Long
Dim i As Integer, j As Integer
SubString = "All Grps"
Lastrow = ThisWorkbook.Worksheets("SalesChannelName").Range("A30000").End(xlUp).Row
For i = 3 To Lastrow
MainString = Range("B" & i)
If InStr(MainString, SubString) <> 0 Then
Rows(Range("A" & i).Row + 1 & ":" & Range("A" & i).Row + 2).Insert
End If
Next i
End Sub
答
要通过所有工作表循环您的代码,请使用下面的代码。
Sub stringcheck()
Dim MainString As String
Dim SubString As String
Dim Lastrow As Long, Lcount As Long
Dim i As Integer, j As Integer
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
ws.Activate
SubString = "All Grps"
Lastrow = ActiveSheet.Range("A30000").End(xlUp).Row
For i = 3 To Lastrow
MainString = Range("B" & i)
If InStr(MainString, SubString) <> 0 Then
Rows(Range("A" & i).Row + 1 & ":" & Range("A" & i).Row + 2).Insert
End If
Next i
Next ws
End Sub
答
你可以试试这个...
Sub stringcheck()
Dim ws As Worksheet
Dim MainString As String
Dim SubString As String
Dim Lastrow As Long, Lcount As Long
Dim i As Integer, j As Integer
Application.ScreenUpdating = False
SubString = "All Grps"
For Each ws In ActiveWorkbook.Worksheets
Lastrow = ws.Cells(Rows.Count, 1).End(xlUp).Row
For i = Lastrow To 3 Step -1
MainString = ws.Range("B" & i)
If InStr(MainString, SubString) <> 0 Then
ws.Range("A" & i + 1).Resize(2).EntireRow.Insert
End If
Next i
Next ws
Application.ScreenUpdating = True
End Sub
+0
谢谢。这是一个非常有创意的方式,在其中更改代码并使其遍历所有页面:) –
+0
@KokoEtokebe不客气!如果这照顾你的原始问题,请花一点时间接受答案,将你的问题标记为已解决。 :) – sktneer
谢谢你,但对LASTROW = thisworkbook.worksheets( “Saleschannelname”)的范围( “A30000”)的东西。最终(xlup ).row,它指定另一个工作表中的最后一个单元格。我估计会导致错误 –