VBA macro concatenate 2 columns in new column-CodePudding

I want to create a macro that inserts new column with column name (BL & Container) and then concatinates 2 column in newly inserted column. In this column I named BL & Container is a new column added my macro. Further I want the macro to concatenate the values present in column H and F macro should find column H and F by column name and concatenate the them in to newly inserted column I.

My codes below

Sub insert_conc()

Dim ColHe As Range
Dim FindCol As Range
Dim con As String
Dim x As Long


Set FindCol = Range("1:1") 'Looks in entire first row.
Set ColHe = FindCol.Find(what:="BL/AWB/PRO", After:=Cells(1, 1))
  
With ActiveWorkbook.Worksheets("WE")

  ColHe.Offset(0, 1).EntireColumn.Insert
  ColHe.Offset(0, 1).Value = "WER"
  'x = Range("A" & Rows.Count).End(xlUp).Row
  con = "=H2&""-""&F2"
  ColHe.Resize(x - 1).Formula = con
    
End With

Application.ScreenUpdating = True


End Sub

[![Error in code][3]][3]

In this code line " con = "=H2&""-""&F2"" please advise how do I update column nameinstead of H2 and F2 macro should find columna H2 and F2 header name and then concatinate the values in newly inserted column I BL & container. Please advise.

CodePudding user response：

Please, use the next adapted code:

Sub insert_conc()
 Dim sh As Worksheet, x As Long, ColHe As Range
 Dim FindCol As Range, con As String, firstCell As Range

 Set sh = Worksheets("LCL")
 x = sh.Range("A" & sh.rows.count).End(xlUp).row

 Set FindCol = sh.Range("1:1") 'Looks in entire first row.
 Set ColHe = FindCol.Find(what:="BL/AWB/PRO", After:=sh.cells(1, 1))

  ColHe.Offset(0, 1).EntireColumn.Insert
  ColHe.Offset(0, 1).value = "BL & Container"
  Set firstCell = ColHe.Offset(1, -2) ' determine the cell to replace F2

  con = "=" & ColHe.Offset(1).Address(0, 0) & "&" & firstCell.Address(0, 0)
  ColHe.Offset(1, 1).Resize(x - 1).Formula = con
End Sub

It is also good to know that using With ActiveWorkbook.Worksheets("LCL") makes sense only if you use it in the code lines up to End with. And your code did not do that... It should be used before, in order to deal with the appropriate sheet, even if it was not the active one.