Help with loop

[kima]

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

Sub ChangeIt(
Dim sTextString As Strin
sTextString = ActiveCell.Valu

Dim sLeftSide As Strin
Dim sRightSide As Strin

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

'Convert to a double data type
Dim dblConverted As Doubl

'Right side requires a diviso
Dim sDivisor As Strin
sDivisor = "1
Dim i As Intege
For i = 1 To Len(sRightSide
sDivisor = sDivisor & "0
Next

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

ActiveCell.Value = dblConverte
End Su

Thank You in Advanc

Ki

NEED MORE HELP....this changes decimal point to comma but it removes th
"-" 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 dat
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 comma
which is specified in my Windows regional settings to be the decima
symbol.

This can of course be done by selecting the cells and use th
edit-replace funtion, but since I do this every week, I tried to faste
things up by making it a macro instead. The problem now is that Exce
won´t recognise the numbers changed by the macro as real numbers, eve
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
suggestion of how to change the macro so it would work, I would be ver
happy

I use this code for the macro

Range("C34:BF34").Selec
Selection.Replace What:=".", Replacement:=",",
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=Fals

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 th
changed values as numbers, even though they perfectly correctly contai
commas

 

[Claus Busch]

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

 

[joeu2004]

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).

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"

 

[Claus Busch]

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

 

[joeu2004]

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

Hans has demonstrated [....]

my name is Claus ;-)

Of course. My bad! Sorry.