DaveRushton2
02-21-2008, 03:16 AM
Hello
I am really struggling on what I think is an easy solution...
I have a number of cells which I want to be able to select all together, and then run a macro which will remove all commas and decimal places, apart from the last decimal place. I have written the code to remove the characters, but I can only get it to act on the first cell in the range of cells that I have selected with the mouse.
The code is below...
Sub RemoveComma()
Dim strInput As String
Dim strTemp As String
Dim rngCell As Range
ActiveCell.CurrentArray.Select
For Each rngCell In CurrentArray
' Removes all commas from a string of text
strInput = ActiveCell.Value
Test:
If InStr(strInput, ",") = 0 Then
ActiveCell.FormulaR1C1 = strInput
Else
strInput = Left(strInput, InStr(strInput, ",") - 1) _
& Right(strInput, Len(strInput) - InStr(strInput, ","))
GoTo Test
End If
' Removes all dots from a string of text except the decimal place
Test2:
If InStr(strInput, ".") = 0 Then
ActiveCell.FormulaR1C1 = strInput
Else
strTemp = Left(strInput, InStr(strInput, ".") - 1) _
& Right(strInput, Len(strInput) - InStr(strInput, "."))
If InStr(strTemp, ".") = 0 Then
strInput = strInput
Else
strInput = strTemp
GoTo Test2
End If
End If
ActiveCell.FormulaR1C1 = strInput
Next rngCell
End Sub
At the moment, it brings up an error due to the 'current array' syntax. What should I be using?
Thanks for the help
Dave
I am really struggling on what I think is an easy solution...
I have a number of cells which I want to be able to select all together, and then run a macro which will remove all commas and decimal places, apart from the last decimal place. I have written the code to remove the characters, but I can only get it to act on the first cell in the range of cells that I have selected with the mouse.
The code is below...
Sub RemoveComma()
Dim strInput As String
Dim strTemp As String
Dim rngCell As Range
ActiveCell.CurrentArray.Select
For Each rngCell In CurrentArray
' Removes all commas from a string of text
strInput = ActiveCell.Value
Test:
If InStr(strInput, ",") = 0 Then
ActiveCell.FormulaR1C1 = strInput
Else
strInput = Left(strInput, InStr(strInput, ",") - 1) _
& Right(strInput, Len(strInput) - InStr(strInput, ","))
GoTo Test
End If
' Removes all dots from a string of text except the decimal place
Test2:
If InStr(strInput, ".") = 0 Then
ActiveCell.FormulaR1C1 = strInput
Else
strTemp = Left(strInput, InStr(strInput, ".") - 1) _
& Right(strInput, Len(strInput) - InStr(strInput, "."))
If InStr(strTemp, ".") = 0 Then
strInput = strInput
Else
strInput = strTemp
GoTo Test2
End If
End If
ActiveCell.FormulaR1C1 = strInput
Next rngCell
End Sub
At the moment, it brings up an error due to the 'current array' syntax. What should I be using?
Thanks for the help
Dave