Solved

Insert a column and formula

Posted on 2011-09-26
7
156 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
[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
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

730 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