VBA Text To columns

dan8825

New Member
Joined
Dec 24, 2018
Messages
26
Hi, Below images show green error messages;

1606135345643.png


When I perform manually and record text to columns it fixes all the errors converting to currency. But when I run the macro, the macro runs without error but does not fix the green error..... Below is the code

VBA Code:
    Columns("C:C").Select
    Selection.TextToColumns Destination:=Range("C1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True

Any ideas why the macro does not result in success?

Thanks
 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,564
Office Version
  1. 365
Platform
  1. Windows
They are not necessarily errors. They are often informational, and show up when you have a text entry that looks like could be a numeric entry (and it is alerting you that maybe you want to make it numeric so you can perform mathematical functions on it).

I think in your example, the issue is that each column has a mixture of numeric (currency) and text entries.
On import, Excel is going to choose either General or Text, and because you have some Text entries in those columns, it is choosing Text, which then renders you numeric (currency) entries as text.

Here is a link on what those green triangles represent, and how you can get rid of them: Best Excel Tutorial - How to remove green triangles from cells?
 

dan8825

New Member
Joined
Dec 24, 2018
Messages
26
Thanks for the advice on removing the green triangles, but I need to resolve the 'error' still

The issues is straight after in the code is

VBA Code:
    Columns("C:C").Select
    Selection.SpecialCells(xlCellTypeConstants, 1).Select
    Selection.Clear

Which deletes everything that is 'currency' so without the correct formatting this fails, again just before the text to columns code, I do apply code to convert all to Currency as below.

VBA Code:
        Columns("B:C").Select
    Selection.NumberFormat = "$#,##0.00"

I have never seen something that when I manually perform works without issue, but when the macro runs it although no error arises it does not perform the action.

Thanks
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,564
Office Version
  1. 365
Platform
  1. Windows
Can you show me before and after images of your data in column C (before and after the macro runs), along with what your desired output should look like?

It would be helpful if you could post the images using this tool here: XL2BB - Excel Range to BBCode , as that will allow me to copy/paste your data to my worksheet, so I can easily recreate your scenario on my side, and try out your code and see exactly what happens.
 

dan8825

New Member
Joined
Dec 24, 2018
Messages
26

ADVERTISEMENT

Thanks,

Before B & C are 'General' text type

1606143395940.png


When are run the macro it stays like this, however when I perform the text to columns manually it looks like

1606143522431.png



VBA Code:
        Columns("B:C").Select
    Selection.NumberFormat = "$#,##0.00"
    Columns("B:B").Select
    Selection.TextToColumns Destination:=Range("B1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("C:C").Select
    Selection.TextToColumns Destination:=Range("C1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    
    Columns("C:C").Select
    Selection.SpecialCells(xlCellTypeConstants, 1).Select
    Selection.Clear
    Columns("B:B").Select
    Selection.SpecialCells(xlCellTypeConstants, 1).Select
    Selection.Clear

This is the full code where issues arise, it formats to currency, then does text to columns as the formating alone does not make the change required. The bottom section then deletes all the cells that are currency but is running into a debug error as no cells can be found.

This code I have inherited from a colleague so not too familiar with it all. I do think though if I can get the code to convert B&C to currency then it will not error at the bottom.

Thanks
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,564
Office Version
  1. 365
Platform
  1. Windows
It would be helpful if you could post the images using this tool here: XL2BB - Excel Range to BBCode , as that will allow me to copy/paste your data to my worksheet, so I can easily recreate your scenario on my side, and try out your code and see exactly what happens.
Are you not able to do this?
This would allow me to copy and paste the actual data directly from this post into Excel on my side, so I can easily recreate your situation on my side.
You cannot copy and paste data out of pictures like posted.
 

dan8825

New Member
Joined
Dec 24, 2018
Messages
26
Sorry for the late response, I cannot I'm afraid due to the type of data. Thanks for all your help however.

I have played around a bit more with it the original file is a CSV, when I run the macro to open the CSV it does not work.
If I run a macro to convert the CSV to .xlsx and run macro it does not work
However, if I convert to .xlsx manually and run the macro it works, very odd.

Thanks again
 

Watch MrExcel Video

Forum statistics

Threads
1,127,341
Messages
5,624,109
Members
416,011
Latest member
chengkoonwing

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top