+ Reply to Thread
Results 1 to 3 of 3

VBA code that I wrote seems to be looping without a Do...Loop statement. Why?

  1. #1
    Registered User
    Join Date
    04-15-2011
    Location
    Lafayette, LA
    MS-Off Ver
    Excel 2007
    Posts
    4

    VBA code that I wrote seems to be looping without a Do...Loop statement. Why?

    I have to run reports from excel files on a regular basis for a certain project. The data comes to me with comma seperated values in a column . I needed to find a way to remove the leading and trailing commas and spaces without removing the commas and spaces from the middle of the strings. I was able to write something in VBA that works, but I can't figure out why it works.

    Sample data
    sample what I need the data to look like (also what the function i wrote is actually giving me)
    ,, asdf , lkjh , , asdf , lkjh
    , asdf, asdf
    , asdf , asdf
    , asdf , asdf

    Here is the function that I wrote

    Please Login or Register  to view this content.
    What I can't figure out is why it is catching multiple commas and a spaces at the beginning and end of the string without a DO Loop.
    Last edited by carpe.cervisiam; 01-07-2012 at 12:06 AM.

  2. #2
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: I got it working, but I don't know what I did.

    carpe.cervisiam,

    It is looping becuase you have the function calling itself:
    Please Login or Register  to view this content.
    That means every time it makes a correction, the function calls itself, so it loops over and over until the last Else statement where it no longer calls itself, but simply sets itself to the already cleaned string
    Hope that helps,
    ~tigeravatar

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  3. #3
    Registered User
    Join Date
    04-15-2011
    Location
    Lafayette, LA
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: I got it working, but I don't know what I did.

    Thank You! I know just enough about VBA to be dangerous and this one was making my brain hurt.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1