Code to format Numbers

[howard]

I am looking for VBA code the will convert numbers that appear with a minus at the end of the number for eg 2,384.34- to be be converted to a negative in front of the number for eg (2,384.34)

See Example below

Your assistance will be most appreciated

Volvo Parts Statement Template.xls
	K	L	M	N
2	4,384.21	97
3	2,384.34-	97
4	1,457.38	25-
5	31.4	25-
6	2,140.09	25-
7	6,450.84	25-
8	2,228.08	25-
9	846.45	25-
10	164.79	25-
11	77.52	25-
12	12,323.74	25-
Sheet1

 

[Akinrotimi]

Assuming the data you want to ammend is on cell k2,paste this code on L2 and drag down through the lenght of your data.:

=VALUE(RIGHT(k2,1)&LEFT(k2,FIND("-",k2)-1))

Regards

'Rotimi

 

[Yahya]

try this

Sub Test()
Dim LR As Long
LR = Range("a" & Rows.Count).End(xlUp).Row
With Range("a1:a" & LR)
    .NumberFormat = "#,##0.00;#,##0.00"
End With
End Sub

 

[howard]

Hi Rotimi & Yahya thanks for the help.

I have adpated Rotimi'a formula as follows:

=IFERROR(VALUE(RIGHT(K2,1)&LEFT(K2,FIND("-",K2)-1)),VALUE(K2))

I would like VBA code that will copy this formula into M2 and then copy this down in line with the last cell containing data in Col K

Once the formula chas been copied into in col M and must then be cut and paste using Paste Special Values into K2 onwards

Your assistance in this regard will be most appreciated


Regards

Howard

 

[Akinrotimi]

OK.

Run the code below to resolve your issue:
=======================================================
Application.ScreenUpdating = False
Range("M2").Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(VALUE(RIGHT(RC[-2],1)&LEFT(RC[-2],FIND(""-"",RC[-2])-1)),VALUE(RC[-2]))"
Range("M2").Select
Selection.AutoFill Destination:=Range("M2:M50000"), Type:=xlFillDefault
Range("M2:M5000").Select
Range("M2").Select
Columns("K:K").Select
Selection.Copy
Columns("O:O").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Columns("O:O").Select
Selection.TextToColumns Destination:=Range("O1"), DataType:=xlFixedWidth, _
FieldInfo:=Array(0, 1), TrailingMinusNumbers:=True
Range("J1").Select
Columns("o").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
Columns("o").Select
Selection.Delete
Range("M2").Select
Columns("M:M").Select
Selection.NumberFormat = "0"
Columns("M:M").EntireColumn.AutoFit
Columns("M:M").EntireColumn.AutoFit
Selection.NumberFormat = "#,##0.00000"
Columns("K:K").Select
Columns("M:M").ColumnWidth = 10.86
Columns("M:M").EntireColumn.AutoFit
Range("K1").Select
Application.ScreenUpdating = True

===================================================


Regards

Rotimi

 

[MARK858]

Howard, if your objective is just to move the trailing minus numbers in column K you should be able to use the code below rather than doing all the copy/pasting depending on your version of excel

Sub Macro3()
 Columns("K:K").TextToColumns Destination:=Range("K:K"), TrailingMinusNumbers:=True
Selection.NumberFormat = "#,##0"
End Sub

 

[Akinrotimi]

Howard, if your objective is just to move the trailing minus numbers in column K you should be able to use the code below rather than doing all the copy/pasting depending on your version of excel

Sub Macro3()
 Columns("K:K").TextToColumns Destination:=Range("K:K"), TrailingMinusNumbers:=True
Selection.NumberFormat = "#,##0"
End Sub

Mark,

For the trailing minus all he needed was this:

=IFERROR(VALUE(RIGHT(K2,1)&LEFT(K2,FIND("-",K2)-1)),VALUE(K2))


He however wanted to some other work on the data.Please read the thread completely for a full understanding.

Cheers!

Rotimi

 

[MARK858]

My apologises

 

[howard]

Hi Guys

Thanks for the input, this is much appreciated

Regards

Howard

 

[Jonmo1]

Try Highlighting a column of numbers
Click Data - Text To Columns
Deliminated - Next
Next
On the 3rd screen, click Advanced, Check "Trailing minus for negative numbers"
Finish

Hope that helps.