+ Reply to Thread
Results 1 to 10 of 10

loop, do while, with variables - help

  1. #1
    Forum Contributor
    Join Date
    09-12-2013
    Location
    chicago, illinois
    MS-Off Ver
    Excel 2010
    Posts
    109

    loop, do while, with variables - help

    hi all, due to my limited knowledge and experience with loops i am hoping to get some help to further my abilities. i have 36 variables...some have values and some dont. this changes based on input. so for example, this time cpn1 may = " test ", but next time may = nothing. what i am looking to do is loop through variables cpn1 - cpn36 and if the variable has a value, copy that value to the last used cell in a column. its not much but i have this:

    Please Login or Register  to view this content.
    thank you for any input !!!!

  2. #2
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    5,066

    Re: loop, do while, with variables - help

    Can show the piece of code where cpn1 is used in your example.
    Have a look on
    Please Login or Register  to view this content.
    to have the last cell used in column "C"
    Please Login or Register  to view this content.
    To loop only if cpn1 is not empty

  3. #3
    Forum Contributor
    Join Date
    09-12-2013
    Location
    chicago, illinois
    MS-Off Ver
    Excel 2010
    Posts
    109

    Re: loop, do while, with variables - help

    thanks for the input, this looks to work for cpn1 variable, is there a way to automatically go to the next one....cpn2, cpn3, cpn4, cpn5, etc all the way to cpn36? .... may have figured it out....would i enter in
    Please Login or Register  to view this content.
    after this??

  4. #4
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: loop, do while, with variables - help

    Quote Originally Posted by fredderf81 View Post
    thanks for the input, this looks to work for cpn1 variable, is there a way to automatically go to the next one....cpn2, cpn3, cpn4, cpn5, etc all the way to cpn36?
    If I understand what you are doing, I think you are going to want to make cpn an array, so you can easily loop through the different values.
    Please Login or Register  to view this content.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  5. #5
    Forum Contributor
    Join Date
    09-12-2013
    Location
    chicago, illinois
    MS-Off Ver
    Excel 2010
    Posts
    109

    Re: loop, do while, with variables - help

    .....bingo!....thank you so much to both of you for your input! sorry if it was confusing, had a tough time explaining it =) works great!!!

  6. #6
    Forum Contributor
    Join Date
    09-12-2013
    Location
    chicago, illinois
    MS-Off Ver
    Excel 2010
    Posts
    109

    Re: loop, do while, with variables - help

    i'm sorry, i spoke too soon, i get For control value already in use...
    Please Login or Register  to view this content.
    i ran it like this:
    Please Login or Register  to view this content.
    and get a type mismatch error i have declared all the cpn variables as strings

    any ideas?

  7. #7
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: loop, do while, with variables - help

    It appears that you at least kind of understand the first error. You cannot use a For..Next control variable within the loop.

    I have not used the Array() function very much, but I don't think it works with variables like that. From what I recall, the Array() function only works with constants [=cpnindex=array("text1","text2",...)]

    Since none of the code you have posted indicates how or where you are assigning values to cpn1,cpn2, etc., I'm not sure exactly how you will adapt this. My first thought would be replace whatever code you are currently using to assign values to cpn1, cpn2, etc. with code to assign those values to cpn(1), cpn(2), etc.

  8. #8
    Forum Contributor
    Join Date
    09-12-2013
    Location
    chicago, illinois
    MS-Off Ver
    Excel 2010
    Posts
    109

    Re: loop, do while, with variables - help

    ugh, i'm sorry again, still learning this stuff...totally my fault. the data in cpn1, cpn2, cpn3, etc comes from txtboxes in a user form. so when i click a button....
    Please Login or Register  to view this content.
    and so on

  9. #9
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: loop, do while, with variables - help

    So, assuming cpn is properly dimensioned, instead use:
    Please Login or Register  to view this content.

  10. #10
    Forum Contributor
    Join Date
    09-12-2013
    Location
    chicago, illinois
    MS-Off Ver
    Excel 2010
    Posts
    109

    Re: loop, do while, with variables - help

    ahhh yes! excellent!! thank you, that is probably what i was missing. i will play around with that a bit.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Incrementing variables in a Loop
    By sungsam in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-17-2011, 11:32 AM
  2. counting variables in a loop
    By Symplystyc in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 06-27-2011, 01:58 AM
  3. Loop through custom variables
    By Khaos176 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-14-2009, 01:45 PM
  4. Can I clear variables within a loop?
    By gshock in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-12-2008, 05:54 PM
  5. [SOLVED] Is it possible to create variables in a loop..
    By shishi in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-03-2006, 03:25 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1