+ Reply to Thread
Results 1 to 12 of 12

If, then inside a for, next statement?

  1. #1
    Registered User
    Join Date
    06-06-2007
    Location
    NYC, NY
    Posts
    66

    Thumbs up If, then inside a for, next statement?

    Hi All,

    I have the below code working but not doing everything that I expect. It counts the number of columns on one sheet and copies the headings to rows to another sheet. One thing is that sometimes there are more than 10 headings and there are only room for 10 rows. So after 10 I need to insert rows. Currently the below code counts and inserts rows correctly but for some strange reason it only copies headings to rows for the first 9. What am I missing here?

    Thanks,
    ~RUTH~

    Please Login or Register  to view this content.
    Last edited by EnergyEngineer; 10-10-2007 at 05:24 PM. Reason: I got the answer so I changed the icon

  2. #2
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200
    No neeed to select the sheets, try this I'm not able to test it without an example workbook.

    Please Login or Register  to view this content.
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  3. #3
    Registered User
    Join Date
    06-06-2007
    Location
    NYC, NY
    Posts
    66

    Thumbs down no that's not it

    No I don't think I need with sheets statements. Everything works with my original code. Except when i is greater than 10 and more rows need to be added. Then rows are added but for some reason the headings are not copied.

    ~RUTH~

  4. #4
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2010, 2019
    Posts
    40,689
    Shouldn't this
    Please Login or Register  to view this content.
    be
    Please Login or Register  to view this content.

  5. #5
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200
    Quote Originally Posted by EnergyEngineer
    No I don't think I need with sheets statements. Everything works with my original code. Except when i is greater than 10 and more rows need to be added. Then rows are added but for some reason the headings are not copied.

    ~RUTH~
    The With .. End With is to avoid unnecessary selecting of sheets. Have you tested the code? An example workbook would help.

  6. #6
    Registered User
    Join Date
    06-06-2007
    Location
    NYC, NY
    Posts
    66

    Red face No, 1st column not needed

    Quote Originally Posted by shg
    Shouldn't this
    Please Login or Register  to view this content.
    be
    Please Login or Register  to view this content.

    shg,

    No, the first column is not needed. So I don't count it in the total number of columns to be copied.

    Thanks though,
    ~RUTH~

  7. #7
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200
    Deducting 1 will not prevent the first colmn being used, but the last. Can you attach a small example?

  8. #8
    Registered User
    Join Date
    06-06-2007
    Location
    NYC, NY
    Posts
    66

    New code, same problem

    Hi Guys,

    Thanks for you help. Below is new code I am using after your comments. I tried reming the select sheet and un unremming your with sheet idea and it never selects the "Detailed Measures" sheet. Instead it just inserts the rows on the "TREAT data" sheet. I have attached a simple version of the problem. Be aware I can't just copy the whole table and paste as transpose b/c there is actually more data in the column that I want and will add more code once I get this row to work.

    Thanks,
    ~RUTH~

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by EnergyEngineer; 10-10-2007 at 01:22 PM. Reason: add attachment

  9. #9
    Registered User
    Join Date
    06-06-2007
    Location
    NYC, NY
    Posts
    66

    It is just a count

    Deducting one does work b/c it just counts the number. That isn't where it starts to copy data from ie the first or the last.

    ~RUTH~

  10. #10
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200
    Try this revised code, by adding a column your rows were constantly moving

    Please Login or Register  to view this content.

  11. #11
    Registered User
    Join Date
    06-06-2007
    Location
    NYC, NY
    Posts
    66

    Unhappy Your code works but not for my application

    Hi Roy,

    I tried your code and it works on the sample file I sent you but not on my original. So thank you so much but I am sorry I didn't give you a better idea of what I am doing. I have modified the sample for you so you can see my problem. I need to insert rows because there are data below and to the right that needs to be preserved and not misaligned.

    Thanks,
    ~RUTH~
    Attached Files Attached Files

  12. #12
    Registered User
    Join Date
    06-06-2007
    Location
    NYC, NY
    Posts
    66

    Thumbs up I got it!

    I figured it out. Maybe not elegant but it works.

    Please Login or Register  to view this content.

+ 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