Improve company productivity with a Business Account.Sign Up

x
?
Solved

Insert a column and formula

Posted on 2011-09-26
7
Medium Priority
?
163 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 2000 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
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
With the functions here, you can parse, convert, and format back and forth between feet and inches and fractions and decimal inches - for normal as well as extreme values and with extreme precision.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

606 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