If you place Option Explicit at the top of the code module this will force you to declare all your variables. I cannot emphasise enough how helpful this will be in debugging your code.
See here on how to make this the default setting:
Declaring Variables
Another debugging technique is to step through your code one line at a time by pressing F8.
I have made some amendment to your code. I have only tested it on column header = "order_id".
Always place your declaration at the top of the procedure. Giving your variable meaningful names helps make your code more legible. Failing that, provide a description of what the variable is for, e.g.,
Rich (BB code):
Sub Macro9()
Dim wsO As Worksheet
Dim LR As Long
Dim i As Integer
Dim iws As Integer
Dim myPosition As Long
Dim mySheets As Variant
Dim myColumns As Variant
Dim xCell As Range
You only need to set the arrays once, so place them outside the loop. To overcome the problem you are having I have placed quotations around the sheet names. Edit if needed.
Rich (BB code):
mySheets = Array("Sheet1", "Sheet3")
myColumns = Array("TransactionID", "order_id", "account", _
"amount", "CurrencyAmount", "SupplierID", _
"UNSPCLV1", "UNSPCLV2", "UNSPCLV3", "UNSPCLV4")
Always comment your code.
The first loop processes the worksheet array
Rich (BB code):
'process worksheet array
For iws = LBound(mySheets) To UBound(mySheets)
Set wsO = Sheets(mySheets(iws))
LR = wsO.Range("A" & Rows.Count).End(xlUp).Row
wsO.Range("A2:A" & LR).NumberFormat = "0"
The inner loop processes the column array:
Rich (BB code):
'process column header array
For i = LBound(myColumns) To UBound(myColumns)
I have used LBound and UBound, i.e., lowerBound and upperBound, to avoid having to worry about manual configuration which may result in overflow errors
You are using Worksheet.Function.Match to determine which column you are working with.
The code may not find a match, so we need an error trap.
Rich (BB code):
'set an error trap in case you don't find the header
On Error Resume Next
myPosition = WorksheetFunction.Match(myColumns(i), wsO.Range("A1:AC1"), 0)
We check a match has been found.
Set the format and convert any value
Reset the position marker.
Rich (BB code):
'check the column header was found
If myPosition <> 0 Then
wsO.Cells(1, myPosition).NumberFormat = "0"
For Each xCell In wsO.Cells(1, myPosition).Range("A2:A" & LR)
xCell.Value = CDec(xCell.Value)
Next xCell
myPosition = 0
End If
I we put all that together we have:
Rich (BB code):
Option Explicit
Sub Macro9()
Dim wsO As Worksheet
Dim LR As Long 'last row in column A of the worksheet
Dim i As Integer 'loop index
Dim iws As Integer 'worksheet counter
Dim myPosition As Long
Dim mySheets As Variant
Dim myColumns As Variant
Dim xCell As Range
mySheets = Array("Sheet1", "Sheet3")
myColumns = Array("TransactionID", "order_id", "account", _
"amount", "CurrencyAmount", "SupplierID", _
"UNSPCLV1", "UNSPCLV2", "UNSPCLV3", "UNSPCLV4")
Application.ScreenUpdating = False
'process worksheet array
For iws = LBound(mySheets) To UBound(mySheets)
Set wsO = Sheets(mySheets(iws))
LR = wsO.Range("A" & Rows.Count).End(xlUp).Row
wsO.Range("A2:A" & LR).NumberFormat = "0"
'process column header array
For i = LBound(myColumns) To UBound(myColumns)
'set an error trap in case you don't find the header
On Error Resume Next
myPosition = WorksheetFunction.Match(myColumns(i), wsO.Range("A1:AC1"), 0)
'check the column header was found
If myPosition <> 0 Then
wsO.Cells(1, myPosition).NumberFormat = "0"
For Each xCell In wsO.Cells(1, myPosition).Range("A2:A" & LR)
xCell.Value = CDec(xCell.Value)
Next xCell
myPosition = 0
End If
Next i
Next iws
MsgBox "Complete"
Set wsO = Nothing
Application.ScreenUpdating = True
End Sub
As I said, I have only tested this on column header = "order_id".
While testing press F8 to step through the code.
Use Alt+Tab to "flick" between the vba editor and Excel.
Hope this helps,
Bertie
