VBA Macro: problems in changing format

ioclio · #1 (**permalink**) 01-18-05, 03:01

Hi,
I'm currently having big problems with this.

- I open a text file, f.i. "mytext.txt", containing numbers. A typical line in the text file may be: "290,25";"299,92";"294,46". To do that I use:

Code:

Workbooks.OpenText Filename:="mytext.txt", StartRow:=1, TextQualifier:="xlDoubleQuote", DataType:="xlDelimited", Semicolon:=True, FieldInfo:=etc. etc.

- Now: I would like to transform numbers which are in text format in numeric format as I want to execute operations on them.
To do that I use:

Code:

Range("D1").Select
Selection.NumberFormat = "0,00"
ActiveCell.FormulaR1C1 = "0"
Range("D1").Copy
Range("A1:C1").PasteSpecial Paste:=xlPasteAll, Operation:=xlPasteSpecialOperationSubtract

but nothing changes, number remains in text format.
The strange thing is that executing the same operations out of a macro, the formats change! The previous code was infact obtained recording a macro and executing the normal excel operations on the range I want to transform.

Where am I wrong?
Thanks for your help.

JanTheDane · #2 (**permalink**) 01-20-05, 05:01

When receiving a data file from UK, the decimal separator is a "." I'm using "," as separator. I use application.Replace but have to enter the cell in order to make the change effective?

The problem does not appear whilst using the replace function without VBA?

Internet Services

Web Development

Digital Marketing

Consumer Tech