title: Mapping Excel columns with VBA
body: <p>I am currently mapping column names to variables in excel and then using the variables in my formula for new rows, here is an example example;</p>
  
  <pre><code>Dim posType as String
  
  Cells.Find(What:="PositionType", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
      :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
      False, SearchFormat:=False).Activate
  
  
  posType = ActiveCell.EntireColumn.Address(False, False)
  posType = Left(posType, InStr(1, posType, ":") - 1)
  
  
  Sheets("sheet1").Range("A1").Select
  
  ActiveCell.End(xlToRight).Select
  
  ActiveCell.Offset(0, 1).Select
  Selection.FormulaR1C1 = "PTH Size"
  
   ' PTH SIZE
  Cells.Find(What:="PTH SIZE", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
      :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
      False, SearchFormat:=False).Activate
      ActiveCell.Offset(1, 0).Select
  
  Selection.Formula = _
    "=IF(" &amp; posType &amp; "2=""PTH""," &amp; settlementDateQuantity &amp; "2,0)"
  
  pthSize = ActiveCell.EntireColumn.Address(False, False)
  pthSize = Left(pthSize, InStr(1, pthSize, ":") - 1)
  r = ActiveCell.Row
  
  With Worksheets("sheet1").Range(pthSize &amp; r)
      .AutoFill Destination:=Range(pthSize &amp; r &amp; ":" &amp; pthSize &amp; lastrow&amp;)
  End With
  </code></pre>
  
  <p>The file I import can change from day to day in the order of their columns but they allays use the same names, currently I am using the find method to get the column then saving the letter to a string and using the reference in my formula however the process of using find and mapping 100 columns is pretty slow...</p>
  
  <p>I have been trying to come up with a better method to do this but cant think of anything faster, could anyone help me out by suggesting a possibly faster way of doing this?</p>
  
  <p>I am also considering moving my project across to EXCEL DNA, does anyone know how much faster it would make my macros run? </p>
  
  <p>Thanks</p>