For Each cell in a pre-selected range

[DaveRushton2]

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

[Bob Phillips]

Sub RemoveComma()
Dim strInput As String
Dim strTemp As String
Dim rngCell As Range

For Each rngCell In Selection
' Removes all commas from a string of text
strInput = rngCell.Value
Test:
If InStr(strInput, ",") = 0 Then
rngCell.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
rngCell.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
rngCell.FormulaR1C1 = strInput
Next rngCell

End Sub

[Bob Phillips]

This is more efficient



Sub RemoveComma()
Dim Pos As Long
Dim rngCell As Range

For Each rngCell In Selection
' Removes all commas from a string of text
With rngCell

.Value = Replace(.Value, ",", "")
Pos = InStrRev(.Value, ".")
If Pos > 0 Then

.Value = Replace(.Value, ".", "~", Pos)
.Value = Replace(.Value, ".", "")
.Value = Replace(.Value, "~", ".")
End If
End With
Next rngCell

End Sub

[DaveRushton2]

Fantastic -worked a treat.
Thanks for the help
Dave