+ Reply to Thread
Results 1 to 15 of 15

Loop to run through collumns

  1. #1
    Registered User
    Join Date
    02-12-2008
    Location
    Portugal
    Posts
    26

    Loop to run through collumns

    Hi,

    I am having some problems in setting up a macro that is intended to do the following

    Run for every cell in a collumn a check if cell =2
    If true then B1 is copied to that cell.
    If false then nothing

    This seems rather simple but the loop should go to collumn B, C, D, E, F,... until the last collumn.

    Every time the collumn shfits the cell to be copied also shifts so is B1, C1, D1, E1, F1,...


    any ideas how to set it up?

  2. #2
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591
    Hi

    Try this

    Please Login or Register  to view this content.
    rylo

  3. #3
    Registered User
    Join Date
    02-12-2008
    Location
    Portugal
    Posts
    26
    I am having problems running the code.

    Maybe im not adding the correct loop to it.

    The checking should run to every cell and in this case its not acting/checking any cell!

    Here is the file but keep in mind that it's taking over every collumn in the sheet.
    Attached Files Attached Files
    Last edited by Glytch; 04-16-2008 at 04:05 AM.

  4. #4
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591
    Hi

    I put the code in a general module, and ran on the sheet and it worked for me.

    What exactly are you doing with the code?

    rylo

  5. #5
    Registered User
    Join Date
    02-12-2008
    Location
    Portugal
    Posts
    26
    Thanks for the help.

    If i increase the lenght of the collumns used the code doesnt work.

    In the attachment i have a small sample but i cannot attach such big file.
    Can you try to test what i mean.. using all collumns? It works fine with the small sample... strange!!
    Last edited by Glytch; 04-16-2008 at 06:49 PM.

  6. #6
    Registered User
    Join Date
    02-12-2008
    Location
    Portugal
    Posts
    26
    Yep... if all collums are used then it doesnt work.
    if i delete last collumn it works fine!

    Go figure.

    Big thanks to you! Excelent support!

  7. #7
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591
    Hi

    Sorry, I didn't realise that you actually may be using the last column in the workbook. You would have to do a test on cell 1 in the last column, and if it is not blank, then use that column, otherwise use the method I used to find the last used column.


    rylo

  8. #8
    Registered User
    Join Date
    02-12-2008
    Location
    Portugal
    Posts
    26
    Now im faced with a diferent challenge fo this file.

    All of the scatered cells must be taken to collumn B.

    Any help on how i should proceed?
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    12-17-2007
    Posts
    9

    Smile Hope this helps

    Glytch, hope this helps solve your problem
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    02-12-2008
    Location
    Portugal
    Posts
    26
    Experiencing some download problems of the attachment.... or the file i get has no VBA code.

    Can you please check or post the code here?

    Thanks
    Last edited by Glytch; 04-17-2008 at 10:37 AM.

  11. #11
    Registered User
    Join Date
    02-12-2008
    Location
    Portugal
    Posts
    26
    Any ideas?

    Thanks

  12. #12
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591
    Hi

    Try
    Please Login or Register  to view this content.

    rylo

  13. #13
    Registered User
    Join Date
    02-12-2008
    Location
    Portugal
    Posts
    26
    Worked perfect.

    One question in "ce" refers to cell or is a undefined name that you set?

    Thanks a lot

  14. #14
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591
    Hi

    ce is a variable in the for each loop. From the help file
    Variable used to iterate through the elements of the collection or array. For collections, element can only be a Variant variable, a generic object variable, or any specific object variable. For arrays, element can only be a Variant variable.
    What we are doing is looking at each ce in the range, testing it, then performing the actions required where applicable.

    rylo

  15. #15
    Registered User
    Join Date
    02-12-2008
    Location
    Portugal
    Posts
    26
    I can only say thanks again for the excelent support.

+ 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