none
Macro to insert Columns with values RRS feed

  • Question

  • Hi All,

    I am new to writing macros to excel. I want to add 2 columns in my sheet with default values.

    I have data like this:

    No.     Name            Role1      Role2

    1      Bob Morgan          St         Emp

    2      Jack Low         St         Emp

    I want to add 2 cols between "No." and "Name" cols with values "A" and "B" as a default.

    Thanks in advance
    Wednesday, February 25, 2015 6:58 AM

Answers

  • Hi,

    I also added the automation for the values. Please see below code.

    Sub Test()
    
    Dim i As Integer
    Dim y As Integer
    
    i = 0
    y = 0
    
    Range("B:B").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("B:B").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    
    Range("B1") = "A"
    Range("C1") = "B"
    
    Range("B2").Select
    
    While ActiveCell.Offset(i, -1) <> ""
    
        If ActiveCell.Offset(i, -1) <> "" Then
            ActiveCell.Offset(i, 0).Value = "School"
            i = i + 1
        Else
            Exit Sub
        End If
        
    Wend
         
    Range("c2").Select
    
    While ActiveCell.Offset(y, 1) <> ""
    
        If ActiveCell.Offset(y, 1) <> "" Then
            ActiveCell.Offset(y, 0).Value = "City"
            y = y + 1
        Else
            Exit Sub
        End If
        
    Wend
    End Sub

    Regards,

    Reshma


    Please Vote as Helpful if an answer is helpful and/or Please mark Proposed as Answer or Mark As Answer when question is answered

    • Proposed as answer by L.HlModerator Friday, March 6, 2015 10:04 AM
    • Marked as answer by L.HlModerator Monday, March 9, 2015 5:03 AM
    Wednesday, February 25, 2015 9:46 AM

All replies

  • Hi All,

    I am new to writing macros to excel. I want to add 2 columns in my sheet with default values.

    I have data like this:

    No.     Name            Role1      Role2

    1      Bob Morgan          St         Emp

    2      Jack Low         St         Emp

    I want to add 2 cols between "No." and "Name" cols with values "A" and "B" as a default.

    Thanks in advance
    • Merged by L.HlModerator Wednesday, February 25, 2015 10:23 AM repeat cases
    Wednesday, February 25, 2015 7:02 AM
  • Hi FKhodaei,

    This is a simple way to achieve above question. If you have more restrictions, please let us know.

    Sub Test()
    
    Range("B:B").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    
    Range("B1") = "A"
    Range("C1") = "B"
    
    End Sub
    

    Regards,

    Reshma


    Please Vote as Helpful if an answer is helpful and/or Please mark Proposed as Answer or Mark As Answer when question is answered

    Wednesday, February 25, 2015 8:04 AM
  • Dear Reshma

    Thanks for anwser

    Its ok but I want these two columns create with a static values in all rows, I mean creating column with title "A" and "B" with Values "School"  and "City".

    I appreciate  

    Wednesday, February 25, 2015 8:27 AM
  • Hi,

    You can do this manualy, so why do you want to do it using vba?

    Tell us what's your real problem or you never get a solution. We are no visionaries.


    Markus Schmid

    Wednesday, February 25, 2015 8:51 AM
  • Hi,

    I also added the automation for the values. Please see below code.

    Sub Test()
    
    Dim i As Integer
    Dim y As Integer
    
    i = 0
    y = 0
    
    Range("B:B").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("B:B").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    
    Range("B1") = "A"
    Range("C1") = "B"
    
    Range("B2").Select
    
    While ActiveCell.Offset(i, -1) <> ""
    
        If ActiveCell.Offset(i, -1) <> "" Then
            ActiveCell.Offset(i, 0).Value = "School"
            i = i + 1
        Else
            Exit Sub
        End If
        
    Wend
         
    Range("c2").Select
    
    While ActiveCell.Offset(y, 1) <> ""
    
        If ActiveCell.Offset(y, 1) <> "" Then
            ActiveCell.Offset(y, 0).Value = "City"
            y = y + 1
        Else
            Exit Sub
        End If
        
    Wend
    End Sub

    Regards,

    Reshma


    Please Vote as Helpful if an answer is helpful and/or Please mark Proposed as Answer or Mark As Answer when question is answered

    • Proposed as answer by L.HlModerator Friday, March 6, 2015 10:04 AM
    • Marked as answer by L.HlModerator Monday, March 9, 2015 5:03 AM
    Wednesday, February 25, 2015 9:46 AM