Help with loop
 

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! :confused:

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.

Help with loop
 

Hi Kima,

Am Sun, 4 Nov 2012 16:30:49 +0000 schrieb kima:

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

try:

Sub ChangeIt()
Dim LRow As Long

LRow = Cells(Rows.Count, "G").End(xlUp).Row

With Range("G1:G" & LRow)
.Replace What:=".", Replacement:=",", _
LookAt:=xlPart, SearchOrder:=xlByRows
.NumberFormat = "0.00"
.TextToColumns Destination:=Range("G1"), _
DataType:=xlFixedWidth, FieldInfo:=Array(0, 1), _
TrailingMinusNumbers:=True
End With

End Sub

Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Help with loop
 

"kima" wrote:
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.
[....]
I use this code for the macro:
Range("C34:BF34").Select
Selection.Replace What:=".", Replacement:=",", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False

If you use one of the "wizards" to import the data, in the last dialog box
(where the Finish button is), you should see an Advance button.

Click on Advance, and change the Decimal Separator to ".".

That will cause the wizard to interpret "." as the decimal separator in the
data. The "." will be changed to "," automagically when the
now-properly-interpreted number is displayed in the cell.

You might also want to change the Thousands Separator to "," (I presume).


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

Hans has demonstrated how to do this efficiently without resorting to a
loop.

But for your edification for other situations in the future, here is one way
to make a loop that converts the thousands separator from "," to " " (space)
and the decimal separator from "." to ",".

Sub doit()
Dim myrng As Range, v As Variant
Dim i As Long, j As Long
Set myrng = Range("C34:BF34")
v = myrng
For i = 1 To UBound(v, 1): For j = 1 To UBound(v, 2)
v(i, j) = CDbl(Replace(Replace(v(i, j), ",", " "), ".", ","))
Next j, i
myrng = v
End Sub

Note: This presumes that C34:BF34 is formatted as General or some other
numeric format. To be sure, you could add the following line before ``myrng
= v``.

myrng.NumberFormat = "General"

Help with loop
 

Hi Joe,

Am Sun, 4 Nov 2012 14:44:24 -0800 schrieb joeu2004:

Hans has demonstrated how to do this efficiently without resorting to a
loop.

my name is Claus ;-)


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Help with loop
 

"Claus Busch" wrote:
Am Sun, 4 Nov 2012 14:44:24 -0800 schrieb joeu2004:
Hans has demonstrated [....]

my name is Claus ;-)

Of course. My bad! Sorry.