?
Solved

Excel 2010 macro to insert column

Posted on 2011-10-27
6
Medium Priority
?
407 Views
Last Modified: 2012-05-12
I am creating a macro that is supposed to insert a new column C, then copy the contents of a different column into this new column.   It works fine when I create the macro, but then when I go test the macro, it does not actually insert the new column C, so the incorrect column is then copied into column C.   Here is the macro code.  

' test1 Macro
'

'
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    ActiveWorkbook.Save
    Range("C2").Select
    ActiveCell.FormulaR1C1 = "=IF((RC[3]=""""),RC[2],RC[3])"
    Range("C2").Select
    Selection.Copy
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Range("C2:C9555").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    ActiveWorkbook.Save
    Range("I2").Select
    ActiveCell.FormulaR1C1 = "=TRIM(CLEAN(RC[-1]))"
    Range("I2").Select
    Selection.Copy
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    ActiveWorkbook.Save
End Sub

Thanks - Eric
0
Comment
Question by:ejpren
  • 2
  • 2
  • 2
6 Comments
 
LVL 17

Accepted Solution

by:
andrewssd3 earned 2000 total points
ID: 37038776
I'm not 100% clear what you're asking, but I think the problem may be that this a recorded macro, and when you started recording you had the whole of column C selected.  The first line of this macro inserts cells based on the current selection, so adding this line at the top may help:
Activesheet.Columns("C:C").Select

Open in new window

0
 
LVL 17

Expert Comment

by:andrewssd3
ID: 37038872
If you are interested, this code shows how to do the same thing in more idiomatic VBA - it's also much quicker as it does not rely on the selection to do its work:
Sub Easier()

    Dim rngFill As Excel.Range

    Set rngFill = ActiveSheet.Range("C2")
    rngFill.EntireColumn.Insert

    ' set rngFill back to col C and to 9554 rows long
    Set rngFill = rngFill.Offset(0, -1).Resize(9554, 1)
    
    ' set the formula in all the cells in the range
    rngFill.FormulaR1C1 = "=IF((RC[3]=""""),RC[2],RC[3])"
    
    ' move it across to column I (ie 6 cols to the right) - it keeps its dimensions
    Set rngFill = rngFill.Offset(0, 6)
    ' set the formula in all the cells in the range
    rngFill.FormulaR1C1 = "=TRIM(CLEAN(RC[-1]))"

    ActiveWorkbook.Save

End Sub

Open in new window

0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 37039127
ejpren, Is this what you are trying?

Option Explicit

Sub Sample()
    Dim lastRow As Long
    
    '~~> Change Sheet Name Here
    With Sheets("Sheet1")
        '~~> Insert a new column in "C"
        .Columns(3).Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
        
        '~~> Get the lastrow of C
        lastRow = .Range("C" & Rows.Count).End(xlUp).Row
        .Range("C2:C" & lastRow).FormulaR1C1 = "=IF((RC[3]=""""),RC[2],RC[3])"
        
        '~~> Get the lastrow of I
        lastRow = .Range("I" & Rows.Count).End(xlUp).Row
        .Range("I2:I" & lastRow).FormulaR1C1 = "=TRIM(CLEAN(RC[-1]))"
    End With
End Sub

Open in new window


Sid
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Closing Comment

by:ejpren
ID: 37039808
Perfect!
0
 

Author Comment

by:ejpren
ID: 37039991
andrewssd3: - both of your answers work.    I will look at them and learn!

SiddharthRout: - your answer initially gave "Run-time error '9': Subscript out of range" debug shows it errored at :   With Sheets("Sheet1").  Changed the code to the actual sheet name.  Re-ran; it did not copy the values into column C, but I am sure we could get there eventually.  

Thanks to all, and I hope to learn more!
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 37040111
>>>your answer initially gave "Run-time error '9': Subscript out of range" debug shows it errored at :   With Sheets("Sheet1").

That is obvious. Isn't it? I left comments in the code ;)

>>> Re-ran; it did not copy the values into column C, but I am sure we could get there eventually.  

It works fine for me. :)

Anyways, glad your query is solved...

Sid
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question