Insert column using VBA

[who am i]

Hey Everyone!! I am a novice but really want to learn. Here is my code

Sub trial()

Dim colm As Long
Dim nwb As Workbook, wb As Workbook
Dim nwk As Worksheet, wk As Worksheet, wk1 As Worksheet
Dim Sales_Rep As Long

co1m = WorksheetFunction.Match("Sales Representative Name", Sheets("Sheet1").Rows(1), 0)

Columns(co1m).Select

Columns(ActiveCell.Column + 1).Insert Shift:=xlToRight

Sales_Rep = ActiveCell.Column + 1



End Sub

Ok so far a column was found and a column was inserted to the right and named. In the co1m column there are names, and I want to paste a vlookup formula in the Sales_rep column that looks up the name in the same row and references a table on sheet2 A:B. I want it to paste formulas in Sales_Rep only up until the last row with a name in it, not to the end of the worksheet. I want to use r1c1 at least in this section, because additional columns could be added/deleted from the source workbook. I welcome questions and might ask for clarification even if a solution works so I can apply it in other needs. Thank you

 

[Peter_SSs]

Welcome to the MrExcel board!

I am a novice but really want to learn.

So firstly a few general tips

• I suggest that in the vba window you 'Require Variable Declaration' so that you do not end up with the situation you have in your code here where you have declared a variable "colm' (third character is a lower case L) but in your code you have used "co1m" (third character is the digit one). It may not have caused a problem in this code but it could easily do so. The error is easily avoided with the setting I mentioned and is applied just once here
  
  
  
  
  
  
• It is very rare that you need to "select" anything in vba and doing so can considerably slow your code so try to avoid "Select" where possible (as I have below)
  
  
• Where vba has an inbuilt way to do something it is generally better to use that way than invoke things like "WorksheetFunction"
  
  
• When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug & copy. My signature block below has more details. I have added the tags for you this time.

Anyway, give this a try with a copy of your workbook.

Sub trial_v2()
  Dim colm As Long
  
  With Sheets("Sheet1")
    colm = .Rows(1).Find(What:="Sales Representative Name", LookAt:=xlWhole, MatchCase:=False).Column
    .Columns(colm + 1).Insert
    .Range(.Cells(2, colm + 1), .Cells(Rows.Count, colm).End(xlUp).Offset(, 1)).FormulaR1C1 = "=VLOOKUP(RC[-1],Sheet2!C1:C2,2,0)"
  End With
End Sub

 

[who am i]

Thank you so much that makes perfect sense. One question off the top. Why/when do you use "with" statements?

 

[Peter_SSs]

You're welcome.

One question off the top. Why/when do you use "with" statements?

Use the With statement when you want to perform a number of statements on a particular object without requalifying the name of the object each time.
So if I had not used 'With' I would have had to specify Sheets("Sheet1") five times instead of once.
It is more efficient for writing and more efficient for the code as it only needs to work out which sheet once..

Using With

With Sheets("Sheet1")
  colm = .Rows(1).Find(What:="Sales Representative Name", LookAt:=xlWhole, MatchCase:=False).Column
  .Columns(colm + 1).Insert
  .Range(.Cells(2, colm + 1), .Cells(Rows.Count, colm).End(xlUp).Offset(, 1)).FormulaR1C1 = "=VLOOKUP(RC[-1],Sheet2!C1:C2,2,0)"
End With

Not using With

colm = Sheets("Sheet1").Rows(1).Find(What:="Sales Representative Name", LookAt:=xlWhole, MatchCase:=False).Column
Sheets("Sheet1").Columns(colm + 1).Insert
Sheets("Sheet1").Range(Sheets("Sheet1").Cells(2, colm + 1), Sheets("Sheet1").Cells(Rows.Count, colm).End(xlUp).Offset(, 1)).FormulaR1C1 = "=VLOOKUP(RC[-1],Sheet2!C1:C2,2,0)"

 

[who am i]

Also how does the magic of only pasting as far as there is data work?

You're welcome.


Use the With statement when you want to perform a number of statements on a particular object without requalifying the name of the object each time.
So if I had not used 'With' I would have had to specify Sheets("Sheet1") five times instead of once.
It is more efficient for writing and more efficient for the code as it only needs to work out which sheet once..

Right on, so if all the actions will take place in the same sheet is the entire vba encased in one with statement? Whats best practice?

 

[Peter_SSs]

Also how does the magic of only pasting as far as there is data work?

.Range(.Cells(2, colm + 1), .Cells(Rows.Count, colm).End(xlUp).Offset(, 1)).FormulaR1C1 = "=VLOOKUP(RC[-1],Sheet2!C1:C2,2,0)"

The range starts in row 2 of the blank column (colm + )
.. and finishes at the bottom cell of the sales rep names column (colm), except ...
.. move right one column - .Offset(, 1) - to put the end point on that same last row but in the new blank column