Excel macro help needed

[Hurricane]

Seems I´ve been getting lots of problems lately.

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.

I attach a screenshot so you can see for yourselves: note that all values have commas (cell formatting in all cells is "General"), but that the sum value only takes the values that have been corrected "by hand", i.e. the sum is 1,09 instead of 1,58.

 

[ainwood]

The problem here is that the replace function is specifically for text. When you use it inside a macro, Excel will treat the converted values as text. You can see this by the fact that the value you converted via the macro appears on the left-hand side of the cell (default for text) and the "numbers" appear on the right (default for numbers).

The problem here is that Excel works on the Windows Regional settings.

Can you confirm what your Regional Settings are? You say that you have these set to the American system (decimal points for decimals, rather than commas), in which case I am very surprised that even the top two values add correctly!

What I would presume is the following:

* You have your regional settings set to European - such that the decimal separator is a comma.

* The data you are importing in to Excel is actually a text file. You are dumping the data into your sheet (maybe unknowingly!) as text.

* When you manually change the decimal point to a comma, Excel recognises this, and believes that you are changing a text cell to a value cell.

* When the macro changes it, as stated above, Excel keeps it as a text cell.


So what to do? Its actually a bit complicated. What you need to do, is read in the text value. You then need to split out the parts that will become the whole number and the decimal section, then convert each of those parts into something that Excel regognises as a number.

Try this:

[color=blue]Sub[/color] ChangeIt()
[color=blue]Dim[/color] sTextString [color=blue]As String[/color]
sTextString = ActiveCell.Value

[color=blue]Dim[/color] sLeftSide [color=blue]As String
Dim[/color] sRightSide [color=blue]As String[/color]

[color=green]'Split text into whole and decimal parts[/color]
sLeftSide = [color=blue]Left[/color](sTextString, [color=blue]InStr[/color](1, sTextString, ".") - 1)
sRightSide = [color=blue]Right[/color](sTextString, [color=blue]Len[/color](sTextString) - 1 - Len(sLeftSide))

[color=green]'Convert to a double data type:[/color]
[color=blue]Dim[/color] dblConverted [color=blue]As Double[/color]

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

dblConverted = [color=blue]CDbl[/color](sLeftSide) + [color=blue]CDbl[/color](sRightSide) / [color=blue]CDbl[/color](sDivisor)

ActiveCell.Value = dblConverted
[color=blue]End Sub[/color]

This may work. Adapt as required to do multiple values - you will need to loop through each cell in the range, rather than do it on a multiple cell selection.



There may be an easier way of doing this - but I couldn't think of one. the CDbl function on its own doesn't seem to work (it doesn't recognise the decimal part....)

It may actually be easier to adapt your input macro that dumps the data into Excel, so that it pastes it as a value rather than as text.

 

[Hurricane]

Originally posted by ainwood
What I would presume is the following:

* You have your regional settings set to European - such that the decimal separator is a comma.

* The data you are importing in to Excel is actually a text file. You are dumping the data into your sheet (maybe unknowingly!) as text.

* When you manually change the decimal point to a comma, Excel recognises this, and believes that you are changing a text cell to a value cell.

* When the macro changes it, as stated above, Excel keeps it as a text cell.

You are absolutely right.

There may be an easier way of doing this - but I couldn't think of one. the CDbl function on its own doesn't seem to work (it doesn't recognise the decimal part....)

Thanks a lot. Your solution works perfectly.