Solved

Insert a column and formula

Posted on 2011-09-26
7
153 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!
0
Comment
Question by:Shanan212
  • 5
  • 2
7 Comments
 
LVL 13

Author Comment

by:Shanan212
ID: 36601059
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
 
LVL 24

Accepted Solution

by:
StephenJR earned 500 total points
ID: 36601118
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
 
LVL 13

Author Comment

by:Shanan212
ID: 36601132
    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
Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

 
LVL 13

Author Closing Comment

by:Shanan212
ID: 36601134
Thanks!
0
 
LVL 24

Expert Comment

by:StephenJR
ID: 36601139
Split the points!?
0
 
LVL 13

Author Comment

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

Thanks!
0
 
LVL 13

Author Comment

by:Shanan212
ID: 36601203
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

Featured Post

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

786 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