Make Your Microsoft Dynamics Investment Count & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.
Course Of The Month
6 days, 5 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
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!
[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
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
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
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation)
David Miller (dlmille)
Synopsis
In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This article describes a serious pitfall that can happen when deleting shapes using VBA.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data.
Rather than update each graph to point to a different set within a static set of data, t…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
Suggested Courses
Course of the Month6 days, 5 hours left to enroll
705 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.