Excel: Array for Multiple Sheets??



Sub Macro9()
Dim wsO As Worksheet
Dim LR As Long
Dim i As Integer
Dim iws As Integer
Dim myPosition As Long

Application.ScreenUpdating = False

mySheets = Array(Sheet1, Sheet3)

For iws = 0 To UBound(mySheets) - 1

myColumns = Array("TransactionID", "order_id", "account", "amount", "CurrencyAmount", "SupplierID", "UNSPCLV1", "UNSPCLV2", "UNSPCLV3", "UNSPCLV4")
LR = Range("A" & Rows.Count).End(xlUp).Row

    For i = 0 To UBound(myColumns) - 1
        
        myPosition = WorksheetFunction.Match(myColumns(i), wsO.Range("A1:AC1"), 0)
               
        wsO.Cells(1, myPosition).Range("A2:A" & LR).Select

        Selection.NumberFormat = "0"
For Each xCell In Selection
    xCell.Value = CDec(xCell.Value)
 Next xCell
    
    Next i
    
    Next iws
    
Set wsO = Nothing
Application.ScreenUpdating = True

MsgBox "Complete"
End Sub
Im driving myself mad now, the array for the columns was compiling, but as soon as I add in the Array to use multiple sheets it all goes horribly wrong. And I am not sure what I am doing that is causing the problem. This is the first of several modules I need to create for a data tidy up and would appreciate if someone can point out the errors of my way. Thanks in advance. Stuart


This question generated 25 answers. To proceed to the answers, click here.

This thread is current as of August 11, 2014.


For more resources for Microsoft Excel