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
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
This question generated 25 answers. To proceed to the answers, click here.
This thread is current as of August 11, 2014.