VBA Text To columns

[dan8825]

Hi, Below images show green error messages;

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

    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

 

[Joe4]

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]

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

    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.

        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]

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]

Thanks,

Before B & C are 'General' text type

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

        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]

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]

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