Help to automate Text to Columns VBA

[brucesw]

Hello Excel World,

Can someone help with some VBA lines to automate this "Text to Columns" process;
https://support.office.com/en-gb/ar...-numbers-40105f2a-fe79-4477-a171-c5bad0f0a885

I've tried to edit a recorded step because the column I want to convert is not fixed so my code searches across the table headings until it finds the "Current Cost" text. I think the problem is in the Selection or in the Destination but cannot figure it out. Here's my code;

Range("A1").Select
Do Until ActiveCell = "Current Cost"
If ActiveCell = "" Then Exit Sub
Selection.Offset(0, 1).Select
Loop
'??? Selection.EntireColumn.Select
Selection.Offset(1, 0).Select
Range(Selection, Selection.End(xlDown)).Select
'???
Selection.TextToColumns Destination:=ActiveCell, DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
'???
Selection.NumberFormat = "$#,##0"

Thanks in advance,

Bruce

 

[Fluff]

Try

Dim Fnd As Range

Set Fnd = Range("1:1").find("Current Cost", , , xlWhole, , , False, , False)
If Not Fnd Is Nothing Then
   Fnd.EntireColumn.TextToColumns Destination:=Fnd, DataType:=xlDelimited, _
      TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
      Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
      :=Array(1, 1), TrailingMinusNumbers:=True
   Intersect(Fnd.EntireColumn, ActiveSheet.UsedRange).NumberFormat = "$#,##0"
End If

 

[brucesw]

Thanks for the response Fluff but I'm afraid I get the same result as before.
It doesn't change the content.
Any other ideas?

Just to check I've tried to record then run over the current location (column AC), without success;

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

 

[Fluff]

What is the content & what are you trying to do?

 

[brucesw]

Thanks again,

It's a sheet extracted from an application to Excel. The Current Cost column used to export as a value but there's been a change and the vendor refuses to edit the export to Excel. There are 2,000 rows, 67 columns.

It comes as "£20,000.00" etc Left justified and behaving as text, just like the MS help page expects. My macro takes the sheet and formats it to something readable (removes wrapping, groups the columns no one ever reads, plus there are options during the macro to delete columns for a simpler output or remove a class of records ("closed"). It's pretty simple really.

 

[Fluff]

Interesting

I setup a test & recorded a macro to do the conversion with TextToColumns, which worked.
But when I re-ran the recorded macro nothing happens.

 

[brucesw]

"You got that right..." I've searched again and solutions going back 6 years provide the same solution.

 

[Fluff]

Give this ago

Dim Fnd As Range
Dim cl As Range
Set Fnd = Range("1:1").find("Latitude", , , xlWhole, , , False, , False)
If Not Fnd Is Nothing Then
   For Each cl In Intersect(ActiveSheet.UsedRange, Fnd.EntireColumn)
      cl.Value = Application.Substitute(cl.Value, "£", "")
   Next cl
   Intersect(Fnd.EntireColumn, ActiveSheet.UsedRange).NumberFormat = "$#,##0"
End If

If it works I'll have a look at improving it.

 

[brucesw]

Only just managed to get back to this Fluff, thanks for the solution.
I appreciate your point that it could be improved but as the "For" loop completes instantaneously, it's not something I need to improve.
Thanks again,

 

[Fluff]

Glad to help & thanks for the feedback