Helo everyone. I have a small scipt of code (courtesy of some developers from this forum) that I have used to automate a task. I have further updated that code to perform additional requirements. However one requirment I need I have not been able to figure.
As you can tell from the code below the variable constCPSelection is valued as a string and that is fine. However I need the ranges "K:K", "L:L", and "M:M" to be formated as numerical values. I added some code at the end (please see comment), and clearly it does not work. Any suggestions?
Better put in the worksheet constFileNameMTMReport
that is created - I need columns K, L and M to be formatted with a comma and two decimal points.
Public Sub CommandButton1_Click() Dim constFilePath As String Dim constFileNameMTMReport As String Dim constFileNameDataSource As String Dim constCPSelection As String '--------------------------------------- constFilePath = "N:\AAAAAAAAA\Application Delivery\Assignments and Projects\XXXXXXX\YYYYYYY\Product\SSSSSSSSS\ZZZZZZZZ\Communication\Portfolio list\" constFileNameMTMReport = txtCPNameBox.Text & "_mtm_portfolio_report.xls" constFileNameDataSource = "mtm_report_asof_" & Format(txtDateBox.Text, "yyyymmdd") & ".xls" With Workbooks.Add .SaveAs constFilePath & constFileNameMTMReport Range("A1") = "Cust" Range("B1") = "ExternalTradedID" Range("C1") = "ProductGroup" Range("D1") = "DeskID" Range("E1") = "BookID" Range("F1") = "Type" Range("G1") = "Type" Range("H1") = "MaturityDate" Range("I1") = "USDNotional" Range("J1") = "CcyPair" Range("K1") = "RecMTM" Range("L1") = "PayMTM" Range("M1") = "MTM" Range("N1") = "COB" Range("O1") = "TradeDate" Range("P1") = "Threshold" Range("P1") = "Min Trans Amt" End With Workbooks.Open constFilePath & constFileNameDataSource constCPSelection = Application.InputBox(prompt:="Please highlight (or select) the area you want with your mouse", Type:=8).Address Workbooks(constFileNameMTMReport).Sheets(1).Cells(2, 1).Resize(Range(constCPSelection).Rows.Count, Range(constCPSelection).Columns.Count) = Workbooks(constFileNameDataSource).Sheets("MTM Detail").Range(constCPSelection).Value 'This is not doing anything ! With Workbooks(constFileNameMTMReport) Range("K:K").NumberFormat = "#,##0.00" 'Format to a number Range("L:L").NumberFormat = "#,##0.00" 'Format to a number Range("M:M").NumberFormat = "#,##0.00" 'Format to a number End With End Sub
Bookmarks