title: VBA to add excel formula by selecting the column name as range
body: <p>I have a column name as "Validation" but column number keep changing. How can I find this column by the name and take that as range.</p>
  
  <p>Currently below is the macro which I am using which checks the column M and adds the formula for all the cells if column M is not blank.</p>
  
  <p>My new expectation is,</p>
  
  <ol>
  <li>See column M, if has cell value as "BLM" &amp; "CFG" then add the excel
  formula by finding the column name "Validation" for those having
  that cell value as "BLM" &amp; "CFG", skip if blank.</li>
  <li>Change all these formula to cell values</li>
  </ol>
  
  <hr>
  
  <pre><code>Sub test_macro()
      Dim sFormula As String
      Dim rng As Range
      Dim ws2 As Worksheet
  
      sFormula = "=IF(IFERROR(VLOOKUP(RC[-11],'Service ID Master List'!C[-11],1,0),""Fail"")=""Fail"",""Check SESE_ID"","""")&amp;IF(IFERROR(VLOOKUP(RC[-9],Rules!C[-13],1,0),""Fail"")=""Fail"","" | Check SESE_RULE"","""")&amp;IF(TRIM(RC[-5])="""","""",IF(IFERROR(VLOOKUP(RC[-5],Rules!C[-13],1,0),""Fail"")=""Fail"","" | Check SESE_RULE_ALT"",""""))&amp;IF(RC[-7]=""TBD"","" | Check SEPY_ACCT_CAT"","""")"
  
      Set ws2 = ActiveSheet
  
      With ws2
          Set rng = .Range("M2")
          Set rng = .Range(rng, .Cells(.Rows.Count, rng.Column).End(xlUp))
      End With
      rng.SpecialCells(xlCellTypeConstants).Offset(0, 1).FormulaR1C1 = sFormula
      'changing formulas in values
      Columns("N:N").Select
      Selection.Copy
      Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
          :=False, Transpose:=False
      Range("N1").Select
      Application.CutCopyMode = False
  End Sub
  </code></pre>