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.