Insert a column and formula

            
    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!
LVL 13
Shanan212Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Shanan212Author Commented:
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

0
StephenJRCommented:
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

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Shanan212Author Commented: 
    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!
0
Become a CompTIA Certified Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

Learn More!
Shanan212Author Commented:
Thanks!
0
StephenJRCommented:
Split the points!?
0
Shanan212Author Commented:
I can't award points to myself but your solution works as well :)

Thanks!
0
Shanan212Author Commented:
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

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.