Solved

Insert a column and formula

Posted on 2011-09-26

Microsoft Excel

1 solution

Medium Priority

158 Views

Last Modified: 2012-05-12

            
    Dim lEndRow As Long, lStartRow As Long
    Dim FormSh As Worksheet: Set FormSh = Sheets("GL Summary")
    
    With FormSh
        
    lStartRow = .Range("A2").Row
    lEndRow = .Range("A" & .Rows.Count).End(xlUp).Row

    For n = lStartRow To lEndRow
 
    Next
    End With

Open in new window

Hi,

I am moderate in VBA. I have the following code combiled via searching here and some of mine.

AS you can see, I am trying to insert column "A" into a sheet (where col A already exists - but move this to right) This new column would be a combination...such that

= C2&D2

Could you please let me know the coding to insert?

I have tried different codings and each one gives different errors :o

Appreciate it!

Comment

Question by:Shanan212

[X]

Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

Help others & share knowledge
Earn cash & points
Learn & ask questions

Join The Community

7 Comments

LVL 13

Author Comment

by:Shanan212

ID: 366010592011-09-26

Made some improvements on inserting a column. Now I just need to insert the formula!

    Dim lEndRow As Long, lStartRow As Long
    Dim FormSh As Worksheet: Set FormSh = Sheets("GL Summary")
    
    With FormSh
    Set BaseWks = ActiveSheet
    
    Range("A1").Activate
    ActiveCell.EntireColumn.Insert
    Range("A1").Value = "Unique"
    
    lStartRow = .Range("A2").Row
    lEndRow = .Range("A" & .Rows.Count).End(xlUp).Row

    'For n = lStartRow To lEndRow
        


        

   ' Next
    End With

Open in new window

LVL 24

Accepted Solution

by:StephenJR

StephenJR earned 2000 total points

ID: 366011182011-09-26

Does this work for you?

Sub x()

Dim lEndRow As Long, lStartRow As Long
Dim FormSh As Worksheet: Set FormSh = Sheets("GL Summary")
Dim BaseWks As Worksheet

With FormSh
    Set BaseWks = ActiveSheet
    Range("A1").EntireColumn.Insert
    Range("A1").Value = "Unique"
    lStartRow = .Range("A2").Row
    lEndRow = .Range("A" & .Rows.Count).End(xlUp).Row
    .Range("B2", .Range("B" & .Rows.Count).End(xlUp)).Offset(, -1).Formula = "=C2&D2"
End With

End Sub

Open in new window

LVL 13

Author Comment

by:Shanan212

ID: 366011322011-09-26

    Set BaseWks = ActiveSheet
    
    Range("A1").Activate
    ActiveCell.EntireColumn.Insert
    Range("A1").Value = "Unique"
        
    LastRow = BaseWks.UsedRange.Rows.Count - BaseWks.UsedRange.Row + 1
    n = 2
    
    For n = BaseWks.UsedRange.Row To LastRow
        BaseWks.Cells(n, "A").Formula = Range("C" & n).Value & Range("D" & n).Value
    Next n

Open in new window

Solved it as well!

LVL 13

Author Closing Comment

by:Shanan212

ID: 366011342011-09-26

Thanks!

LVL 24

Expert Comment

by:StephenJR

ID: 366011392011-09-26

Split the points!?

LVL 13

Author Comment

by:Shanan212

ID: 366011552011-09-26

I can't award points to myself but your solution works as well :)

Thanks!

LVL 13

Author Comment

by:Shanan212

ID: 366012032011-09-26

For those who are looking for solution, my solution somehow overwrote my heading title, so I moved the 'naming' down

    Set BaseWks = ActiveSheet
    
    Range("A1").Activate
    ActiveCell.EntireColumn.Insert
  
    LastRow = BaseWks.UsedRange.Rows.Count - BaseWks.UsedRange.Row + 1
    
    n = 1
    
    For n = BaseWks.UsedRange.Row To LastRow
        BaseWks.Cells(n, "A").Formula = Range("D" & n).Value & Range("E" & n).Value
    Next n
    
    Range("A1").Value = "Unique"                                            'It renames it here
    BaseWks.Columns.AutoFit

Open in new window

Featured Post

Question has a verified solution.

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

Automated Link Checker

Article by: Josh

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…

Finding the Office Templates Folder

Article by: Helen

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents. The steps to find the Templates folder path are …