Code:
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")
mySheets = Array(1, 3)
Set wsO = Worksheets(mySheets(iws))
myColumns = Array("Deptoff", "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
For Each xCell In wsO.Cells(1, myPosition).Range("A2:A" & LR)
xCell.Value = CDec(xCell.Value)
xCell.NumberFormat = "0"
Next xCell
myPosition = 0
End If
Next i
Next iws
Set wsO = Nothing
Application.ScreenUpdating = True
End Sub
I was able to create this working code and would now like to make it more adapatable by prompting the user to click on a column (or columns) to allow the number formatting to be done by VBA.
I am not able to create a personal workbook (at work) and therefore was thinking along the lines of paste the worksheet to be formatted into Sheet 2 then run the code.
Im struggling to find code that allows multiple selections to be selected by the user.
Any help or direction as always greatly appreciated.
Thanks in advance.
PS This code works a treat so please feel free to use and abuse at your leisure.