I would like to make a loop of this on column G. But cant make the loop.


Sub ChangeIt()
Dim sTextString As String
sTextString = ActiveCell.Value

Dim sLeftSide As String
Dim sRightSide As String

'Split text into whole and decimal parts
sLeftSide = Left(sTextString, InStr(1, sTextString, ".") - 1)
sRightSide = Right(sTextString, Len(sTextString) - 1 - Len(sLeftSide))

'Convert to a double data type:
Dim dblConverted As Double

'Right side requires a divisor
Dim sDivisor As String
sDivisor = "1"
Dim i As Integer
For i = 1 To Len(sRightSide)
sDivisor = sDivisor & "0"
Next i

dblConverted = CDbl(sLeftSide) + CDbl(sRightSide) / CDbl(sDivisor)

ActiveCell.Value = dblConverted
End Sub

Thank You in Advance

Kim

NEED MORE HELP....this changes decimal point to comma but it removes the "-" in front of a negative number..

Anyone have another idea to solve this.

The case: I have an Excel file, into which I import some numerical data on a weekly basis. The imported data uses dots to mark decimals (i.e. 24.15 means 24 euros 15 cents), but I want to change this to commas which is specified in my Windows regional settings to be the decimal symbol.

This can of course be done by selecting the cells and use the edit-replace funtion, but since I do this every week, I tried to fasten things up by making it a macro instead. The problem now is that Excel won´t recognise the numbers changed by the macro as real numbers, even though they have the comma just like any other value in the sheet!

I guess this only applies to European users, but if anybody has a suggestion of how to change the macro so it would work, I would be very happy.

I use this code for the macro:

Range("C34:BF34").Select
Selection.Replace What:=".", Replacement:=",", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False

So, the problem is that if I do the exact same operation "by hand", everything works ok, but if the macro does it, Excel won´t recognise the changed values as numbers, even though they perfectly correctly contain commas.