Results 1 to 7 of 7
  1. #1
    Lounger
    Join Date
    Feb 2011
    Posts
    27
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Just want to insert a column after "Area" cell

    Hi All
    I have a spreadsheet with a header row that goes - "time, Area, time, Area" etc. All I want to do is insert a column to the right of each cell that contains "Area". My code sucessfully cycles through all the cells in the row that have "Time" or "Area" but does not ever insert a column. I have gone so far as to display the cell.value using msgbox and the code is correctly reading the cell contents but for some reason the If...Then... statement is never satisfied.

    It must be something simple but I have tried a half dozen variations and have had no success. Any ideas for a rookie?
    Thanks



    Sub Insertcolumn()

    'use to insert column after "Area" cells

    Range("E3").Activate


    Do While Not IsEmpty(ActiveCell)

    If ActiveCell.Value = "Area" Then
    ActiveCell.EntireColumn.Offset(0, 1).Insert (xlShiftToRight)
    ActiveCell.Offset(0, 2).Select 'move activecell over 2 columns
    End If

    ActiveCell.Offset(0, 1).Select 'move activecell over 1 column

    Loop
    End Sub

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,435
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Minor adjustment worked for me:
    Code:
    Sub Insertcolumn()
    
    'use to insert column after "Area" cells
    
    Range("E3").Activate
    
    
    Do While Not IsEmpty(ActiveCell)
    
      If Trim(ActiveCell.Value) = "Area" Then
        ActiveCell.EntireColumn.Offset(0, 1).Insert (xlShiftToRight)
        ActiveCell.Offset(0, 2).Select 'move activecell over 2 columns
      Else
        ActiveCell.Offset(0, 1).Select 'move activecell over 1 column
      End If
    
    Loop
    
    End Sub
    Attached Images Attached Images
    Last edited by RetiredGeek; 2011-05-19 at 17:53.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    Lounger
    Join Date
    Feb 2011
    Posts
    27
    Thanks
    4
    Thanked 0 Times in 0 Posts
    GC2 R10 NA6 Standards Mar 25 2011.xls
    RG
    Interestingly, when I started with a new spreadsheet and manually entered the headers, the macro worked like a charm. However, it doesnt work on my data files for no discernible reason to me. Other macros have worked just fine so I am stumped. Have a look and see what you think. Thanks!

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,435
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    The reason it didn't is because the cells with "Area" in them actually contain " Area"! That space killed the macro. I've adjusted the code above to fix that problem. It now works fine with your provided sample file.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. The Following User Says Thank You to RetiredGeek For This Useful Post:

    arjay13 (2011-05-20)

  6. #5
    Lounger
    Join Date
    Feb 2011
    Posts
    27
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Wow! I knew it was something goofy but had no clue as to what the issue was. I see that there is a listing of "Thanks". How about Thank you, Thank you, Thank you! Three times in one post. I started programming back in the punch card days (and then stopped for 25 years) and I just knew that it something weird like that. I don't get to SC very often (I was in Aiken two years back at the Savannah River Lab)but I do owe you a cold one! Have a great weekend.

  7. #6
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    Hi arjay13 - Just a note about registering a "Thanks". You can reward RG with a registered thank you by clicking the "Thanks" button on the bottom left of his post.

  8. #7
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,435
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    You're Welcome. It's a shame you don't get down this way often it is really a nice place with nice people. As the sign says on entering Manning, SC "Beautiful Places, Smiling Faces"
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

Posting Permissions

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