CODEWITHSUNDEEP
arraysexcelobjectvba
ThomasRones
ThomasRones

Reputation: 683

Excel VBA Loop through array of strings which are the object names inside the loop

I want to use an array of strings that will replace the Worksheet object inside my loop, but I cant seem to figure it out.

If I declare SheetX as Variant, then I get the Object Required Error

If I declare SheetX as Object, then I get Compile Error: For Each variable on arrays must be variant

Sub DeleteAllData()

'SheetsArray = ["BalanceSheetTransposed", "IncomeStatementTransposed", "CashflowStatement"]
Dim SheetsArray(0 To 2) As Variant
Dim SheetX As Object

SheetsArray(0) = "BalanceSheetTransposed"
SheetsArray(1) = "IncomeStatementTransposed"
SheetsArray(2) = "CashflowStatement"

For Each SheetX In SheetsArray
    lastrow = SheetX.Cells(Rows.Count, 1).End(xlUp).Row
    lastcolumn = SheetX.Cells(1, Columns.Count).End(xlToLeft).Column
    SheetX.Range("A2", Cells(lastrow, lastcolumn)).ClearContents
Next SheetX

End Sub

Upvotes: 0

Views: 5979

Answers (3)

Slai
Slai

Reputation: 22876

The Array has to be passed to the Sheets object :

Sub DeleteAllData()
    Dim ws As Worksheet

    For Each ws In Sheets(Array("BalanceSheetTransposed", "IncomeStatementTransposed", _ 
                                                          "CashflowStatement"))
        s.UsedRange.Offset(1).ClearContents
    Next
End Sub

Upvotes: 0

YowE3K
YowE3K

Reputation: 23984

Your major problem was that you were trying to treat the strings stored in the array as if they were worksheets, but they are just strings.

The simplest way to get around it is to use Worksheets(SheetsArray) to return the worksheets that have the names you want to use, and then loop through those worksheets:

Sub DeleteAllData()

    Dim SheetX As Worksheet
    Dim lastRow As Long
    Dim lastColumn As Long

    Dim SheetsArray(0 To 2) As Variant
    SheetsArray(0) = "BalanceSheetTransposed"
    SheetsArray(1) = "IncomeStatementTransposed"
    SheetsArray(2) = "CashflowStatement"
    'An alternative to the previous 4 lines would be
    'Dim SheetsArray As Variant
    'SheetsArray = Array("BalanceSheetTransposed", _
    '                    "IncomeStatementTransposed", _
    '                    "CashflowStatement")

    'Loop through the worksheets referred to in the array
    For Each SheetX In Worksheets(SheetsArray)

        With SheetX  ' avoids some typing
            lastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
            lastColumn = .Cells(1, .Columns.Count).End(xlToLeft).Column

            'Existing code would have had issue with the unqualified Cells
            'reference in the following line.  You should always qualify Cells
            'to specify which sheet you mean, because it defaults to
            'ActiveSheet.Cells
            .Range("A2", .Cells(lastRow, lastColumn)).ClearContents
        End With

    Next SheetX

End Sub

Upvotes: 1

Whome
Whome

Reputation: 10400

Out of my head 'cause I don't have Excel in this machine. Loop through the strings and set worksheet object.

Sub DeleteAllData()
  Dim SheetsArray(0 To 2) As String
  Dim SheetX As Worksheet
  Dim name as String

  SheetsArray(0) = "BalanceSheetTransposed"
  SheetsArray(1) = "IncomeStatementTransposed"
  SheetsArray(2) = "CashflowStatement"

  For Each name In SheetsArray
    set SheetX = ActiveWorkbook.worksheets(name)
    lastrow = SheetX.Cells(Rows.Count, 1).End(xlUp).Row
    lastcolumn = SheetX.Cells(1, Columns.Count).End(xlToLeft).Column
    SheetX.Range("A2", Cells(lastrow, lastcolumn)).ClearContents
  Next

End Sub

Upvotes: 1

Related Questions