title: Excel VBA Method 'ClearContents' of Object Range Failed
body: <p>I've looked at the similar questions about "Method of Object Range Failed" but none seem to help me solve my problem.</p>
  
  <p>The error I receive is: "Method 'ClearContents' of Object Range Failed"</p>
  
  <p>Essentially what I'm trying to achieve is that if cell value of BG21 = 1 And the length of the string in adjacent cell (BH21) is 0 Then timestamp the adjacent cell (BH21). Ideally I want to repeat this for Range (BG21:BG35). I know I could use a for each method but I want to get this error cleared first.</p>
  
  <p>I don't want to use the cell formula because I need a timestamp the moment the cell BG21 changes to 1. If I use a cell formula then anytime the workbook is opened the timestamp changes. </p>
  
  <p>The code I have is:</p>
  
  <pre><code>Private Sub Worksheet_Change(ByVal Target As Range)
     Dim wb As Workbook, ws As Worksheet
  
     Set wb = ThisWorkbook
     Set ws = wb.Worksheets("Submittal Kickoff Meeting")
  
      If (ws.Range("$BG$21").Value = 1 And len(ws.Range("$BH$21").Value) = 0 And Target.Address = ws.Range("$BG$21")) Then
          ws.Range("$BH$21").Value = Format(Now(), "m/dd/yyyy hh:mm:ss AM/PM")
      Else
          ws.Range("$BH$21").ClearContents  '&lt;--- Error occurs here
      End If
  End Sub
  </code></pre>
  
  <p>The error occurs on line:</p>
  
  <pre><code>ws.Range("$BH$21").ClearContents
  </code></pre>
  
  <p>I have also tried:</p>
  
  <pre><code>ws.Range("$BH$21").Value = ""
  </code></pre>
  
  <p>But I received the same error code</p>
  
  <p>Any advice??</p>
  
  <p>.</p>