title: How to control the value of a Cell in Excel using an IF Then Statement in VBA
body: <p>I am currently working on an excel application where I have a macro, operated by a button click, which resets the numerical values within certain cells in a table.</p>
  
  <p>Within this table there are 3 columns; "Quantity Fitted (n)", "Quantity Required (m)" and "Lock Configuration".</p>
  
  <p>What I need to happen, is when the button is clicked, the numerical values for each line within the "Quantity Fitted (n)" column are reset to match the values displayed within the "Quantity Required (m)" column of the same line. </p>
  
  <p>However, if the cell value within the "Lock Configuration" column of that row is set to "locked", I want the "Quantity Fitted (n)" value to remain unchanged after the button click.</p>
  
  <p>I hope this makes sense! Here's my current attempt to code this:</p>
  
  <pre><code>Public Sub Reset_Quantity_Fitted()
  'Macro to make quantity fitted equal to quantity required
  
      Dim DatRange2 As String
  
      quantity_required = Worksheets(ActiveSheet.Name).ListObjects("Table_" &amp; ActiveSheet.Name).ListColumns("Quantity Required (m)").DataBodyRange
  
      Set DatRange = Worksheets(ActiveSheet.Name).ListObjects("Table_" &amp; ActiveSheet.Name).ListColumns("Quantity Fitted (n)").DataBodyRange
      DatRange2 = Worksheets(ActiveSheet.Name).ListObjects("Table_" &amp; ActiveSheet.Name).ListColumns("Lock Configuration").DataBodyRange
  
      If DatRange2 = "Locked" Then
          DatRange = DatRange
      Else
          DatRange = quantity_required
      End If
  
  End Sub
  </code></pre>
  
  <p>I don't think I'm too far away, but when running this code I get the following error:</p>
  
  <blockquote>
    <p>Run-time error '13': Type mismatch</p>
  </blockquote>
  
  <p>Any help is greatly appreciated! Thank you!</p>
  
  <p>EDIT! Following the advice from BigBen below, I've attempted the following code, but still receive the same mismatch error. Can anybody help?</p>
  
  <pre><code>Public Sub Reset_Quantity_Fitted()
  'Macro to make quantity fitted equal to quantity required
  
      Dim rng As Range
      Dim cell As Range
  
      Set rng = Worksheets(ActiveSheet.Name).ListObjects("Table_" &amp; ActiveSheet.Name).ListColumns("Quantity Fitted (n)").DataBodyRange
  
  For Each cell In rng.Cells
  
      If rng.Offset(, 5) = "Locked" Then
          cell = Worksheets(ActiveSheet.Name).ListObjects("Table_" &amp; ActiveSheet.Name).ListColumns("Quantity Fitted (n)").DataBodyRange
      Else
          cell = Worksheets(ActiveSheet.Name).ListObjects("Table_" &amp; ActiveSheet.Name).ListColumns("Quantity Required (m)").DataBodyRange
      End If
  
  Next cell
  
  End Sub
  </code></pre>