Text to Columns Macro

[TooZippy]

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.

[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]

 

[Logit]

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]

Ditto Logit.

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

Cheerio,
vcoolio.

 

[TooZippy]

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]

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]

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]

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]

.
I dont know what to advised. Your macro works here as is :

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]

What was the error message & number?

 

[TooZippy]

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.