+ Reply to Thread
Results 1 to 9 of 9

Error in Text to columns loop due to parameter

  1. 12-10-2013, 11:41 AM #1
    icyrius
    icyrius is offline
    Forum Contributor
    Join Date
    06-17-2012
    Location
    USA
    MS-Off Ver
    Excel 2016
    Posts
    150

    Error in Text to columns loop due to parameter

    I have a loop that converts texts with spaces to columns if the cell is not empty. However, if the cell has a text with no spaces then loop stops.

    This is how the loop is designed,

    Please Login or Register  to view this content.
    Is there a way to pretest if the text has spaces before starting the loop?

    Thanks
    Reply With QuoteRegister To Reply
  2. 12-10-2013, 11:45 AM #2
    Marc L
    Marc L is offline
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Re: Error in Text to columns loop due to parameter


    You have the choice with functions Instr, InstrRev or Split …
    Reply With QuoteRegister To Reply
  3. 12-10-2013, 11:45 AM #3
    Norie
    Norie is offline
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Error in Text to columns loop due to parameter

    Why not put the If inside the loop?

    Please Login or Register  to view this content.
    If posting code please use code tags, see here.
    Reply With QuoteRegister To Reply
  4. 12-10-2013, 12:02 PM #4
    Andy Pope
    Andy Pope is offline
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,437

    Re: Error in Text to columns loop due to parameter

    Are you sure that is working as expected?

    Assuming RowCounter has a value of 3 then

    .Range("J" & RowCounter) refers to J3

    But Destination:=.Range("L" & RowCounter) refers to U5
    Cheers
    Andy
    www.andypope.info
    Reply With QuoteRegister To Reply
  5. 12-10-2013, 01:25 PM #5
    icyrius
    icyrius is offline
    Forum Contributor
    Join Date
    06-17-2012
    Location
    USA
    MS-Off Ver
    Excel 2016
    Posts
    150

    Re: Error in Text to columns loop due to parameter

    @Marc L
    I will follow up on thiese functions (Instr, InstrRev or Split …). Thanks.

    @ Andy Pope
    I recorded the macro and pasted the code in the module. It is supposed to leave "J" values untouched and paste the text to columns starting at column "L". I do not see how it can reference U5 like you are saying.
    Reply With QuoteRegister To Reply
  6. 12-10-2013, 01:35 PM #6
    Norie
    Norie is offline
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Error in Text to columns loop due to parameter

    Try this.
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  7. 12-10-2013, 01:39 PM #7
    icyrius
    icyrius is offline
    Forum Contributor
    Join Date
    06-17-2012
    Location
    USA
    MS-Off Ver
    Excel 2016
    Posts
    150

    Re: Error in Text to columns loop due to parameter

    @andy Pope you were correct, it is pasting values in "U" and every other row. How can I correct this?
    Reply With QuoteRegister To Reply
  8. 12-10-2013, 01:45 PM #8
    icyrius
    icyrius is offline
    Forum Contributor
    Join Date
    06-17-2012
    Location
    USA
    MS-Off Ver
    Excel 2016
    Posts
    150

    Re: Error in Text to columns loop due to parameter

    @Norie Tested your code and it is sending me to column "U". I still do not understand it.
    Reply With QuoteRegister To Reply
  9. 12-11-2013, 06:17 AM #9
    Andy Pope
    Andy Pope is offline
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,437

    Re: Error in Text to columns loop due to parameter

    This will output all information, split by space, to column L
    Please Login or Register  to view this content.
    The reason you code is outputting to column U is you have used the WITH command.

    As you code is not complete I assume your first with is for the activesheet.

    So for a RowCounter variable with the value of 3 the .range reference is relative to A1 on the sheet.
    Please Login or Register  to view this content.
    For this bit of code the WITH is relative to cell J3.

    So L3 relative to J3 is U5

    Please Login or Register  to view this content.
    in the immediate window the follow demonstrates that
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Macro to loop between multiple columns and copy the text in one column
    By Manish_Gupta in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 11-14-2013, 06:41 AM
  2. [SOLVED] Use VBA to run "text to columns' Loop on first row to convert from text to date format
    By csmiley in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 06-03-2013, 02:06 PM
  3. [SOLVED] Loop through worksheets when splitting text to columns
    By majaover in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 01-27-2013, 03:54 AM
  4. [SOLVED] Nested loop to match cell text for two columns in different sheets
    By lottidotti in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-07-2012, 10:48 AM
  5. Error with passing collection as parameter
    By McManCSU in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-01-2005, 02:05 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1