I use an =IF function
=IF(RIGHT(A1;1)="-";"-"&LEFT(A1;LEN(A1)-1);A1)*1
to shift a minus sign from the end of the cell to the beginning but I'd like to use it in a macro so it is performed on the same column (or the same selection)...
Add a comment
|
1 Answer
1) Use the the For Each.... loop construct to loop through each cell in a range. 2) If you are wanting to convert "numbers" of the 123- to -123 to a proper number and not text, use the Val command to convert a string to a number. Note however that if you if you have cell with something like "ABC-", this will become -ABC which VBA then attempts to convert to a number ...and produces zero as a result
Sub MoveMinus()
Dim c As Range
For Each c In Intersect(Selection, Selection.Worksheet.UsedRange)
If (Right(c, 1) = "-") Then
c = Val("-" & Left(c, Len(c) - 1)) 'Val to make the result numeric
End If
Next
End Sub
2 Comments
teaspoon
Great. Thanks, @HedgePig. Don't worry, all cells are numbers, i.e 123-... How would I add to the macro one step before that to also find all , (comma) and replace them with nothing (delete)?
HedgePig
Glad it helped. To remove any commas, use the Replace command, e.g. c = Replace(c, ",", "") before the If condition. Note that this will replace a comma in text field but NOT "replace" the comma in numbers formatted with commas - as this is a formatting issue