Macro doesn't correctly replace "." with "," and getting Run-time error '1004'.

**H.Gabor** · 07-26-2018, 04:01 AM

Hello everyone,

I have to following macro running in the attached workbook "proba2":

Please Login or Register  to view this content.

First of all, the macro doesn't completely change every "." to "," in the selected range, and also pops up an error message (Run-time error: '1004') referring to the highlighted row of the macro.

Here's a detailed process, what I want the macro to do:

1. Delete the first row
2. Select column H, and divide it to multiple columns using "|" (Alt Gr+W) as a divider.
3. Select the created columns H-K and swap every "." with ","
4. Create a bar graph from column K

Any ideas to make it work?

**kersplash** · 07-26-2018, 04:06 AM

To fix the error message try changing;

Please Login or Register  to view this content.

**kersplash** · 07-26-2018, 04:09 AM

And I can't see any "." in Columns H:K after the macro runs.

**H.Gabor** · 07-26-2018, 04:10 AM

Thank you, yes it works like that, but the name of the sheet might not always be the same, so I wanted to come up with a solution for that.

**H.Gabor** · 07-26-2018, 04:12 AM

That is the problem, I get nothing instead of "," especially in column K. For example a value should be 3,000 after the change, but it shows up like 3 000.

**kersplash** · 07-26-2018, 04:15 AM

I guess Excel thinks 3.000 is 3 and strips the zeroes and comma before you can replace it.

**kersplash** · 07-26-2018, 04:18 AM

When you do your text to columns select Text as the Column Data Format.

**kersplash** · 07-26-2018, 04:23 AM

Try changing these values;

Please Login or Register  to view this content.

**H.Gabor** · 07-26-2018, 04:24 AM

It seems to just get rid of the blank between the first and second digit. Here's my macro now:

Please Login or Register  to view this content.

**kersplash** · 07-26-2018, 04:25 AM

Did you see Post #8?

**H.Gabor** · 07-26-2018, 04:32 AM

It also comes with the same result as the previous Cell Type solution it seems.
It's interesting that for some cells it works but for others it doesn't.

**kersplash** · 07-26-2018, 04:40 AM

Give an example of what doesn't work now? I can't tell from the screenshot.

**H.Gabor** · 07-26-2018, 04:52 AM

If you look at the screenshot, every value from column H-K should be looking like the ones with the little green mark in the top left corner.
So K1 should be 4,7. K2 would be 6,1 etc.

**jindon** · 07-26-2018, 05:04 AM

Do you mean?

Please Login or Register  to view this content.

**H.Gabor** · 07-26-2018, 05:15 AM

This one seems to be working, can you make it so it covers columns K-H? The part when it swaps "." to "," I mean.

**jindon** · 07-26-2018, 05:19 AM

Before anything you need to edit all your posts and add code tags.
It's a rule in the forum.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Click on Edit to open your thread, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

**H.Gabor** · 07-26-2018, 05:51 AM

Sorry, it is now fixed, i think.

**jindon** · 07-26-2018, 05:56 AM

OK, thanks for the code tags.

This should do

Please Login or Register  to view this content.

**H.Gabor** · 07-26-2018, 06:31 AM

This works wonderfully, thank you so much.

**jindon** · 07-26-2018, 06:36 AM

You are welcome and thanks for the rep.

Macro doesn't correctly replace "." with "," and getting Run-time error '1004'.

LinkBack

Thread Tools

Rate This Thread

Display

Macro doesn't correctly replace "." with "," and getting Run-time error '1004'.

Re: Macro doesn't correctly replace "." with "," and getting Run-time error '1004'.

Re: Macro doesn't correctly replace "." with "," and getting Run-time error '1004'.

Re: Macro doesn't correctly replace "." with "," and getting Run-time error '1004'.

Re: Macro doesn't correctly replace "." with "," and getting Run-time error '1004'.

Re: Macro doesn't correctly replace "." with "," and getting Run-time error '1004'.

Re: Macro doesn't correctly replace "." with "," and getting Run-time error '1004'.

Re: Macro doesn't correctly replace "." with "," and getting Run-time error '1004'.

Re: Macro doesn't correctly replace "." with "," and getting Run-time error '1004'.

Re: Macro doesn't correctly replace "." with "," and getting Run-time error '1004'.

Re: Macro doesn't correctly replace "." with "," and getting Run-time error '1004'.

Re: Macro doesn't correctly replace "." with "," and getting Run-time error '1004'.

Re: Macro doesn't correctly replace "." with "," and getting Run-time error '1004'.

Re: Macro doesn't correctly replace "." with "," and getting Run-time error '1004'.

Re: Macro doesn't correctly replace "." with "," and getting Run-time error '1004'.

Re: Macro doesn't correctly replace "." with "," and getting Run-time error '1004'.

Re: Macro doesn't correctly replace "." with "," and getting Run-time error '1004'.

Re: Macro doesn't correctly replace "." with "," and getting Run-time error '1004'.

Re: Macro doesn't correctly replace "." with "," and getting Run-time error '1004'.

Re: Macro doesn't correctly replace "." with "," and getting Run-time error '1004'.

Thread Information

Users Browsing this Thread

Similar Threads

Replace "ActiveSheet.Pictures.Insert" with "Shapes.Addpicture" and "SaveWithDocument"

"Countif" with "left" error, "Sumproduct" picking up not qualifying rows

How do i fix "Run-time error "1004" Application-defined or object-defined error"

[SOLVED] VBA help needed to remove all "/" then replace with "-" from cell "B3"and "B5"

get "run time error 1004" when i try to activate macro on protected sheet why?

[SOLVED] How to Count number of "Error" and "OK" after the word "Instrument" found in table row

Excel 2007 : Macro gives a "run time error '1004'"

Bookmarks

Bookmarks

Posting Permissions