Caveat: my VBA knowledge is self-taught via Googling so please be patient with me! I have a set of worksheets and am trying to perform the same set of actions through each sheet. Having problem with my codes: E.g. worksheets are "Staging AA 101", "Staging AA 102", "Staging AA 201", "Staging BB 101", "Staging BB 102", "Staging BB 201", "Staging CC 101", "Staging CC 102", "Staging CC 201"
My codes are:
Dim ExpSub(1 To 3) As String
Dim Post(1 To 3) As String
Dim i As Integer 'ExpSub
Dim j As Integer 'Post
ExpSub(1) = "101"
ExpSub(2) = "102"
ExpSub(3) = "201"
Post(1) = "AA"
Post(2) = "BB"
Post(3) = "CC"
For j = 1 To 3 'j for Post
For i = 1 To 3 'i for ExpSub
Sheets("Staging " & Post(j) & " " & ExpSub(i)).Select
Range("AK4").Select
Range(Selection, Selection.Offset(100, 1)).Select
Selection.ClearContents
Next i
Next j
Errored out at this line Sheets("Staging " & Post(j) & " " & ExpSub(i)).Select
How do i loop through all the sheets with such variables please? Thanks in advance!
CodePudding user response:
Your loops work as expected. You should write the result of your string concatenation into an intermediate variable and check with the debugger.
I can think of two reasons why your code fails:
a) One of your sheets is missing or has a typo in its name (maybe an extra space character). Check with the debugger for which sheet your code fails.
b) You don't specify the Workbook you want to work with, so VBA will look for the sheets in the Active Workbook - maybe this is a different workbook.
Beside this: You don't need to Select
a sheet or a range to work with them, and it fact this should be avoided because it makes your code slow and is an open gate for bugs. You should take the time and read How to avoid using Select in Excel VBA.
Your code could look like
Dim ExpSub() As Variant, Post As Variant
ExpSub = Array("101", "102", "201")
Post = Array("AA", "BB", "CC")
Dim i As Integer 'ExpSub
Dim j As Integer 'Post
For j = LBound(Post) To UBound(Post) 'j for Post
For i = LBound(ExpSub) To UBound(ExpSub) 'i for ExpSub
Dim sheetname As String
sheetname = "Staging " & Post(j) & " " & ExpSub(i)
Debug.Print sheetname
With ThisWorkbook.Sheets(sheetname)
Dim r As Range
Set r = .Range("A4").Resize(100, 1)
r.ClearContents
End With
Next i
Next j
The code assumes that the sheets are in the same Workbook as your code (ThisWorkbook
), else you need to change that.
If you like loooong statements, instead of the With
-Block you can write a single line. Personally, I hate such long statements as they are hard to read and if they fail, you don't know which part caused the fail.
ThisWorkbook.Sheets(sheetname).Range("A4").Resize(100, 1).ClearContents
CodePudding user response:
Clear Ranges
Option Explicit
Sub ClearRanges()
Const wsNameLeft As String = "Staging "
Const rgAddress As String = "AK4:AL104"
Dim ExpSub As Variant: ExpSub = VBA.Array("101", "102", "201")
Dim Post As Variant: Post = VBA.Array("AA", "BB", "CC")
Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
Dim ws As Workbook
Dim wsName As String
Dim e As Long
Dim p As Long
For p = 0 To UBound(Post)
For e = 0 To UBound(ExpSub)
wsName = wsNameLeft & Post(p) & " " & ExpSub(e)
On Error Resume Next
Set ws = wb.Worksheets(wsName)
On Error GoTo 0
If Not ws Is Nothing Then ' worksheet found
ws.Range(rgAddress).ClearContents
Set ws = Nothing
Else ' worksheet not found
' Look in the immediate window VBE CTRL G
Debug.Print "Worksheet '" & wsName & "' not found."
End If
Next e
Next p
'Range("AK4").Select
MsgBox "Ranges cleared.", vbInformation
End Sub