I get a file from SAP where the numbers are strangly formatted. Decimal is
dot and in some cells there is also blank space before (xxx0.0 or x254.45)x
means space.
What I need to do is first to change dot to comma since it is standard in
Sweden, then format the cells with commaseparator and 2 decimals.
This is no problem when I do it manually.
When I use Record Macro, it also works when recording, but not when I test
the code.
The result is that all cells are formatted as text and the smarttag gives
information that the cell is either formatted as text or is proceeded with an
apostrof.
Manually I can convert to number but not through code.
I have also tryed to use the funktion TRIM to remove all blanks and to
replace blanks with "nothing". I have tryed Data, text to column without
success.
Everyting I do works manually but not after recording.
Please can anybody help me!?
Lena

1) Use the val() function
2) change formating of cell before writing a number to convert from a string
Range("A1").numberformat = "general"
3) use the formatt function
newnumber = format(val(mynumber),"General")


I think you arre having problems with local formating in Sweeden. I think
there are bugs with excel 2007.


"Lena_Office" wrote:

I get a file from SAP where the numbers are strangly formatted. Decimal is
dot and in some cells there is also blank space before (xxx0.0 or x254.45)x
means space.
What I need to do is first to change dot to comma since it is standard in
Sweden, then format the cells with commaseparator and 2 decimals.
This is no problem when I do it manually.
When I use Record Macro, it also works when recording, but not when I test
the code.
The result is that all cells are formatted as text and the smarttag gives
information that the cell is either formatted as text or is proceeded with an
apostrof.
Manually I can convert to number but not through code.
I have also tryed to use the funktion TRIM to remove all blanks and to
replace blanks with "nothing". I have tryed Data, text to column without
success.
Everyting I do works manually but not after recording.
Please can anybody help me!?
Lena

Hi!
I use Excel 2003 this time but I guess it can be a problem any way. I have
tried now and probably I am not "good enought".
How do I get the val() function to convert a range of cells like (F:L)? Can
you give me a complete code string?
Lena

"Joel" skrev:

1) Use the val() function
2) change formating of cell before writing a number to convert from a string
Range("A1").numberformat = "general"
3) use the formatt function
newnumber = format(val(mynumber),"General")


I think you arre having problems with local formating in Sweeden. I think
there are bugs with excel 2007.


"Lena_Office" wrote:

I get a file from SAP where the numbers are strangly formatted. Decimal is
dot and in some cells there is also blank space before (xxx0.0 or x254.45)x
means space.
What I need to do is first to change dot to comma since it is standard in
Sweden, then format the cells with commaseparator and 2 decimals.
This is no problem when I do it manually.
When I use Record Macro, it also works when recording, but not when I test
the code.
The result is that all cells are formatted as text and the smarttag gives
information that the cell is either formatted as text or is proceeded with an
apostrof.
Manually I can convert to number but not through code.
I have also tryed to use the funktion TRIM to remove all blanks and to
replace blanks with "nothing". I have tryed Data, text to column without
success.
Everyting I do works manually but not after recording.
Please can anybody help me!?
Lena

for each cell in Range("A1:G7")
if cell < "" then
cell = val(cell)
end if
next cell

Is F:L columns?

You could also do
for each cell in Columns("F:L")
if cell < "" then
cell = val(cell)
end if
next cell

"Lena_Office" wrote:

Hi!
I use Excel 2003 this time but I guess it can be a problem any way. I have
tried now and probably I am not "good enought".
How do I get the val() function to convert a range of cells like (F:L)? Can
you give me a complete code string?
Lena

"Joel" skrev:

1) Use the val() function
2) change formating of cell before writing a number to convert from a string
Range("A1").numberformat = "general"
3) use the formatt function
newnumber = format(val(mynumber),"General")


I think you arre having problems with local formating in Sweeden. I think
there are bugs with excel 2007.


"Lena_Office" wrote:

I get a file from SAP where the numbers are strangly formatted. Decimal is
dot and in some cells there is also blank space before (xxx0.0 or x254.45)x
means space.
What I need to do is first to change dot to comma since it is standard in
Sweden, then format the cells with commaseparator and 2 decimals.
This is no problem when I do it manually.
When I use Record Macro, it also works when recording, but not when I test
the code.
The result is that all cells are formatted as text and the smarttag gives
information that the cell is either formatted as text or is proceeded with an
apostrof.
Manually I can convert to number but not through code.
I have also tryed to use the funktion TRIM to remove all blanks and to
replace blanks with "nothing". I have tryed Data, text to column without
success.
Everyting I do works manually but not after recording.
Please can anybody help me!?
Lena