replacement with macro stop values from working in formula

**diederik777** · 12-17-2012, 10:09 AM

hey everyone,

just a small note, im using a bit of an outdated excel version, excel 2003 to be exact.

for a small assignment i have been tinkering with the excel macro function.
the assignment involves importing different text files into 1 excel sheet and formatting them so they can be used in formula's and graphs.

the files that are delivered however use a . instead of a , as decimal seperator. this is where my problem starts.
ive gotten far enough to get everything working with the macro.
but when i use the replace function with CTRL+H to replace all the . for , everything is fine and the values all work in their formula's.

but as soon as i let me macro function replace these . for , the values all stop working.
as soon as i double click on the cell and proceed to click out of the cell, note: not changing anything, the cell works in my formula again.

the bit of code is use to replace the . with , is as follows:

Please Login or Register  to view this content.

the macro im writing is going to be used by other people, so i'd love to find out what causes this problem so that i can automate the process as much as possible.

if anyone knows a solution, i'd very much appreciate the help.
thanks in advance,
Diederik

edit:
i just figured that a possible solution might be to prompt the same window CTRL+H opens using VB.
if anyone knows the code for this, please let me know. ive been fairly new to VB and mostly figuring things out by recording and looking at what kind of code that generates

**Andrew-R** · 12-17-2012, 10:16 AM

Might it just need to recalculate?

Please Login or Register  to view this content.

**diederik777** · 12-17-2012, 10:32 AM

the data that i import is used in the calculations. when i import the data and replace the . with , the formula gives the error DIV/0. it seems as if the values are not seen as numbers untill they are "re-entered" by me focussing on the cell.

the cell where the calculation is in, is on a different sheet and cell. so i did:

Please Login or Register  to view this content.

using this recalculate application did not solve it, is it correct use of this line?

**RHCPgergo** · 12-17-2012, 10:40 AM

I think the values are perceived as text in excel. Try to use the text to columns option (data / text to columns). I think there's a VBA equivalent to it also.

**Andrew-R** · 12-17-2012, 10:41 AM

Can you post your workbook with the data imported and the errors showing? I can't replicate the error.

**diederik777** · 12-17-2012, 10:51 AM

the files are originaly .txt files which are imported into the sheet with the application.GetOpenFileName.

this file contains callibration data for a certain machine it looks like this

Please Login or Register  to view this content.

the user is allowed to select multiple files which are all imported in 1 excel sheet.
using the following code

Please Login or Register  to view this content.

following that is a bunch of code to automaticly sort the data to date and format the area. then comes the code to replace the . with , which was mentioned earlier.

Please Login or Register  to view this content.

the data is imported each on a different column, on the next sheet calculations are done on all the values in each column.
but when using the replace macro the values are not used in the graph or formula, but if i leave out the replacement from the macro and replace . with , using the CTRL+H command everything works fine

edit:
i copied the code from a different forum and seemed to do the job fine. but after inspecting i think it might have something to do with this line

Please Login or Register  to view this content.

**diederik777** · 12-18-2012, 10:32 AM

i have solved the problem, it might be a bit of an inefficient way to do it. but i made a seperate macro which opens, edits and saves all the seperate files.

after which this macro continues and therefor skips the replacement step in the above mentioned macro.

this can be marked as solved or deleted, since the problem is already solved. the code i used to format my single files is as follows:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

doing it this way as seen in the above macro's the extra spaces at the start of lines were trimmed as well as the dots replaced to comma's.

after that my original macro which calls up all the single files and imports them in a single sheet was working correctly.

nonetheless, thanks to everyone who tried to help

ill be sure to come back here if i have more problems