title: Macro to concatenate two columns based on header values
body: <p>I have two columns in an Excel spreadsheet containing many columns. I would like to merge these two columns, but their location in the sheet may vary. This means that I need to utilize the first row column headers to determine which two columns to concatenate. I would like the result to be added as the next column in the sheet.</p>
  
  <p>For example, where "Brand", "Line, and "Product" are in row 1 as column headers, the macro should search for the value in "Brand" and concatenates it with the value in "Model" and puts the result in an inserted column in the sheet (in this case D, but that could change depending on the number of columns). I can't use column or cell references because the position of the Brand and Model columns might change, as well as the number of columns:</p>
  
  <p>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;A&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;|&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;B&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;|&nbsp;&nbsp;&nbsp;&nbsp;C&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;|<br>Brand&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;|&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Line&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;|&nbsp;&nbsp;&nbsp;Model&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;|<br>Pentax&nbsp;&nbsp;&nbsp;&nbsp;|&nbsp;&nbsp;Camera&nbsp;&nbsp;&nbsp;|&nbsp;&nbsp;&nbsp;&nbsp;K-30&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;|<br>Honda&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;|&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Auto&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;|&nbsp;&nbsp;Accord&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;|<br>Apple&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;|&nbsp;Computer&nbsp;|&nbsp;MacBook Air&nbsp;|</p>
  
  <p>Here's what I've tried:</p>
  
  <pre><code>Sub Insrt()
      Dim Model As Range
      Dim Brand As Range
      Dim LastRow As Long
      Set Brand = Rows(1).Find(what:="Brand", LookIn:=xlValues, lookat:=xlWhole)
      Set Model = Rows(1).Find(what:="Model", LookIn:=xlValues, lookat:=xlWhole)
      If Found Is Nothing Then Exit Sub
      LastRow = Cells(Rows.Count, Brand.Column).End(xlUp).Row
      Model.Offset(, 1).EntireColumn.Insert
      Cells(1, Model.Column + 1).Value = "Concatenated_Value"
      Range(Cells(2, Model.Column + 1), Cells(LastRow, Model.Column + 1)).Formula = "=A2&amp;C2"
  End Sub
  </code></pre>
  
  <p>The problem with this approach is that my formula is specifically concatenating columns A &amp; C. Yet, Brand and Model might not be in those specific columns every time - in the future, they may shift locations. How can I improve this so that I'm not explicitly calling out columns A and C, and instead utilize the column headers?</p>