Text to Columns Macro

TooZippy

Board Regular
Joined
Dec 30, 2018
Messages
70
I tried recording a macro and at first it worked then I got trouble with it. I found this macro on the internet and it worked at first, but when I tried to run it today I got the debug box to pop up. Can anybody tell me what is wrong with it and offer any suggestions so that it would work please? The characters in the cells that I want to put in their own column are separated by spaces.

Thank you,

Jared Z.

Code:
[LEFT][COLOR=#1D2228][FONT=Helvetica Neue]Sub Text_to_Columns()[/FONT][/COLOR]
[COLOR=#1D2228][FONT=Helvetica Neue]    Dim objRange1 As Range[/FONT][/COLOR]
[COLOR=#1D2228][FONT=Helvetica Neue]    [/FONT][/COLOR]
[COLOR=#1D2228][FONT=Helvetica Neue]    'Set up the range[/FONT][/COLOR]
[COLOR=#1D2228][FONT=Helvetica Neue]    Set objRange1 = Range("D:D")[/FONT][/COLOR]
[COLOR=#1D2228][FONT=Helvetica Neue]    [/FONT][/COLOR]
[COLOR=#1D2228][FONT=Helvetica Neue]    objRange1.TextToColumns _[/FONT][/COLOR]
[COLOR=#1D2228][FONT=Helvetica Neue]      Destination:=Range("Q2"), _[/FONT][/COLOR]
[COLOR=#1D2228][FONT=Helvetica Neue]      DataType:=xlDelimited, _[/FONT][/COLOR]
[COLOR=#1D2228][FONT=Helvetica Neue]      Tab:=False, _[/FONT][/COLOR]
[COLOR=#1D2228][FONT=Helvetica Neue]      Semicolon:=False, _[/FONT][/COLOR]
[COLOR=#1D2228][FONT=Helvetica Neue]      Comma:=False, _[/FONT][/COLOR]
[COLOR=#1D2228][FONT=Helvetica Neue]      Space:=True, _[/FONT][/COLOR]
[COLOR=#1D2228][FONT=Helvetica Neue]      Other:=True, _[/FONT][/COLOR]
[COLOR=#1D2228][FONT=Helvetica Neue]      OtherChar:="  "[/FONT][/COLOR]
[COLOR=#1D2228][FONT=Helvetica Neue] [/FONT][/COLOR]
[COLOR=#1D2228][FONT=Helvetica Neue]End Sub[/FONT][/COLOR][/LEFT]
 

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
3,598
The macro works consistently here, using 1,000 rows.

Is there an error code that is popping up and if so, what line in the macro is highlighted ?
 

vcoolio

Well-known Member
Joined
Jun 29, 2014
Messages
1,001
Ditto Logit.

I've tested it on 500 rows without any errors arising.

Cheerio,
vcoolio.
 

TooZippy

Board Regular
Joined
Dec 30, 2018
Messages
70
I tried it once yesterday and the debug box popped up. When I clicked debug, just about the lower half of the code highlighted yellow. I will try it again on Monday or Tuesday.

Thank you,


Jared Z.
 

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
3,598

ADVERTISEMENT

The file you are using may have become corrupted.

Copy the macro to NOTEPAD.

Delete the macro in the workbook, then save and close the workbook.

Re-open the workbook, the paste the macro from NOTEPAD to the workbook.

Try it again.
 

TooZippy

Board Regular
Joined
Dec 30, 2018
Messages
70
That is a possibility even though it has not had that much use. I have only used it to set up the macro's and test it. I will try that either Monday or Tuesday and let you know.

Thank you,

Jared Z.
 

TooZippy

Board Regular
Joined
Dec 30, 2018
Messages
70

ADVERTISEMENT

I deleted the macro and closed the workbook. I saved the file in the process. I reopened the workbook and pasted the macro in the VBA. I connected the macro to the button and clicked the button for the macro to run. The debug box came up with the section highlighted yellow from "ObjRange1" to "OtherChar". Any suggestions please???

Jared Z.
 

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
3,598
.
I dont know what to advised. Your macro works here as is :

Code:
Option Explicit


Sub Text_to_Columns()
    Dim objRange1 As Range


    'Set up the range
    Set objRange1 = Range("D:D")


    objRange1.TextToColumns _
      Destination:=Range("Q2"), _
      DataType:=xlDelimited, _
      Tab:=False, _
      Semicolon:=False, _
      Comma:=False, _
      Space:=True, _
      Other:=True, _
      OtherChar:="  "


End Sub


Download workbook : https://www.amazon.com/clouddrive/share/nzjOOIn45KQ0vbO7uXPeNY456ObT2ywg9oqltgmTPQr
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,530
Office Version
  1. 365
Platform
  1. Windows
What was the error message & number?
 

TooZippy

Board Regular
Joined
Dec 30, 2018
Messages
70
There was no error message or number. Just that the debug box came up. When I clicked on debug the code came up with the above mentioned part of the code highlighted yellow. I will try pasting the code that Logit posted above tomorrow and see what happens. The only difference that I see in his code and mine is the line "Option Explicit".

Thank you,

Jared Z.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,348
Messages
5,528,185
Members
409,807
Latest member
nicky736

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top