Just want to insert a column after “Area” cell

Topic

New Reply

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

Reply | Quote

Replies

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

:cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

27985-GC2-R10-NA6-Standards-Mar-25-2011
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!

Reply | Quote

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!
Computer Specs

Reply | Quote

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.

Reply | Quote

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”:cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

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.

Reply | Quote