Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.
Solved
Insert a column and formula
Posted on 2011-09-26
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
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!
-
5
2
7 Comments
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
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
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
Solved it as well!
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
Featured Post
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 Already a member? Login.
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.
Suggested Courses
Course of the Month9 days, 20 hours left to enroll
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.