title: Method 'Range' of object_Worksheet failed error -2147417848 (80010108)
body: <p>I've googled extensively, but can't seem to find anything on my problem. I have a workbook that has various VBA intermingled with in cell formulas. As it sits now it works fine, but if I try and add or modify a simple cell reference such as &quot;=N24&quot; it breaks my code and throws up the error:</p>
  <blockquote>
  <p>Run-time error '-2147417848 (80010108)': Method 'Range' of object'_Worksheet' failed</p>
  </blockquote>
  <p>This happens whether I'm referencing a calculated cell, a user filled cell, or a blank cell.</p>
  <p>Here's the sheet calculate code, which is the only code on this particular sheet. I know it's rudimentary, but usually simple is good. When it throws this error, it breaks at:</p>
  <pre><code>Sheets(&quot;CALCULATIONS&quot;).Range(&quot;N24&quot;).ClearContents
  </code></pre>
  <p>If I remove that code, then it breaks at the first IF statement line.</p>
  <pre><code>Private Sub Worksheet_Calculate()
  Dim SIZE As String
  Dim THICKNESS As Single
  Dim WIDTH As Single
  Dim HEIGHT As Single
  Dim WALL As Single
  Dim WALL1 As String
  Dim OD As Single
  Dim FINALROW As Integer
  Dim i As Integer
  Sheets(&quot;CALCULATIONS&quot;).Range(&quot;N24&quot;).ClearContents
  If ThisWorkbook.Sheets(&quot;SHEET1&quot;).Range(&quot;E4&quot;) = &quot;STRUCTURAL_I_BEAM&quot; And ThisWorkbook.Sheets(&quot;SHEET1&quot;).Range(&quot;F4&quot;) &lt;&gt; 0 Then
      
  Application.ScreenUpdating = False
  Sheets(&quot;IBEAM&quot;).Range(&quot;Q2:Q100&quot;).ClearContents
  SIZE = Sheets(&quot;SHEET1&quot;).Range(&quot;F4&quot;).Value
  FINALROW = Sheets(&quot;IBEAM&quot;).Cells(Rows.Count, 2).End(xlUp).Row
  
      For i = 2 To FINALROW
          If Worksheets(&quot;IBEAM&quot;).Cells(i, 2) = SIZE Then
              Worksheets(&quot;IBEAM&quot;).Cells(i, 8).Copy
              Sheets(&quot;IBEAM&quot;).Range(&quot;Q&quot; &amp; Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
          End If
      Next i
      Worksheets(&quot;CALCULATIONS&quot;).Range(&quot;N24&quot;).Value = Worksheets(&quot;IBEAM&quot;).Range(&quot;Q2&quot;).Value
  Application.ScreenUpdating = True
  End If
  
  
  If ThisWorkbook.Sheets(&quot;SHEET1&quot;).Range(&quot;E4&quot;) = &quot;STRUCTURAL_CHANNEL&quot; And ThisWorkbook.Sheets(&quot;SHEET1&quot;).Range(&quot;F4&quot;) &lt;&gt; 0 Then
      
  Application.ScreenUpdating = False
  Sheets(&quot;CHANNEL&quot;).Range(&quot;Q2:Q100&quot;).ClearContents
  SIZE = Sheets(&quot;SHEET1&quot;).Range(&quot;F4&quot;).Value
  FINALROW = Sheets(&quot;CHANNEL&quot;).Cells(Rows.Count, 2).End(xlUp).Row
  
      For i = 2 To FINALROW
          If Worksheets(&quot;CHANNEL&quot;).Cells(i, 2) = SIZE Then
              Worksheets(&quot;CHANNEL&quot;).Cells(i, 6).Copy
              Sheets(&quot;CHANNEL&quot;).Range(&quot;Q&quot; &amp; Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
          End If
      Next i
      Worksheets(&quot;CALCULATIONS&quot;).Range(&quot;N24&quot;).Value = Worksheets(&quot;CHANNEL&quot;).Range(&quot;Q2&quot;).Value
  Application.ScreenUpdating = True
  End If
  
  
  If ThisWorkbook.Sheets(&quot;SHEET1&quot;).Range(&quot;E4&quot;) = &quot;STRUCTURAL_ANGLE&quot; And ThisWorkbook.Sheets(&quot;SHEET1&quot;).Range(&quot;F4&quot;) &lt;&gt; 0 Then
  
  
  Application.ScreenUpdating = False
  Sheets(&quot;ANGLE&quot;).Range(&quot;Q2:Q100&quot;).ClearContents
  WIDTH = Sheets(&quot;SHEET1&quot;).Range(&quot;F4&quot;).Value
  HEIGHT = Sheets(&quot;SHEET1&quot;).Range(&quot;G4&quot;).Value
  THICKNESS = Sheets(&quot;SHEET1&quot;).Range(&quot;H4&quot;).Value
  FINALROW = Sheets(&quot;ANGLE&quot;).Cells(Rows.Count, 3).End(xlUp).Row
  
      For i = 2 To FINALROW
          If Worksheets(&quot;ANGLE&quot;).Cells(i, 3) = WIDTH And Worksheets(&quot;ANGLE&quot;).Cells(i, 4) = HEIGHT And Worksheets(&quot;ANGLE&quot;).Cells(i, 6) = THICKNESS Then
              Worksheets(&quot;ANGLE&quot;).Cells(i, 7).Copy
              Sheets(&quot;ANGLE&quot;).Range(&quot;Q&quot; &amp; Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
          End If
      Next i
      Worksheets(&quot;CALCULATIONS&quot;).Range(&quot;N24&quot;).Value = Worksheets(&quot;ANGLE&quot;).Range(&quot;Q2&quot;).Value
  Application.ScreenUpdating = True
  End If
  
  
  If ThisWorkbook.Sheets(&quot;SHEET1&quot;).Range(&quot;E4&quot;) = &quot;TUBE_RECTANGLE&quot; And ThisWorkbook.Sheets(&quot;SHEET1&quot;).Range(&quot;F4&quot;) &lt;&gt; 0 Then
  
  
  Application.ScreenUpdating = False
  Sheets(&quot;RECTTUBE&quot;).Range(&quot;Q2:Q100&quot;).ClearContents
  WIDTH = Sheets(&quot;SHEET1&quot;).Range(&quot;F4&quot;).Value
  HEIGHT = Sheets(&quot;SHEET1&quot;).Range(&quot;G4&quot;).Value
  WALL = Sheets(&quot;SHEET1&quot;).Range(&quot;H4&quot;).Value
  FINALROW = Sheets(&quot;RECTTUBE&quot;).Cells(Rows.Count, 3).End(xlUp).Row
  
      For i = 2 To FINALROW
          If Worksheets(&quot;RECTTUBE&quot;).Cells(i, 3) = WIDTH And Worksheets(&quot;RECTTUBE&quot;).Cells(i, 4) = HEIGHT And Worksheets(&quot;RECTTUBE&quot;).Cells(i, 5) = WALL Then
              Worksheets(&quot;RECTTUBE&quot;).Cells(i, 6).Copy
              Sheets(&quot;RECTTUBE&quot;).Range(&quot;Q&quot; &amp; Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
          End If
      Next i
      Worksheets(&quot;CALCULATIONS&quot;).Range(&quot;N24&quot;).Value = Worksheets(&quot;RECTTUBE&quot;).Range(&quot;Q2&quot;).Value
  Application.ScreenUpdating = True
  End If
  
  
  If ThisWorkbook.Sheets(&quot;SHEET1&quot;).Range(&quot;E4&quot;) = &quot;TUBE_SQUARE&quot; And ThisWorkbook.Sheets(&quot;SHEET1&quot;).Range(&quot;F4&quot;) &lt;&gt; 0 Then
  
  
  Application.ScreenUpdating = False
  Sheets(&quot;SQUARETUBE&quot;).Range(&quot;Q2:Q100&quot;).ClearContents
  WIDTH = Sheets(&quot;SHEET1&quot;).Range(&quot;F4&quot;).Value
  WALL = Sheets(&quot;SHEET1&quot;).Range(&quot;H4&quot;).Value
  FINALROW = Sheets(&quot;SQUARETUBE&quot;).Cells(Rows.Count, 3).End(xlUp).Row
  
      For i = 2 To FINALROW
          If Worksheets(&quot;SQUARETUBE&quot;).Cells(i, 3) = WIDTH And Worksheets(&quot;SQUARETUBE&quot;).Cells(i, 5) = WALL Then
              Worksheets(&quot;SQUARETUBE&quot;).Cells(i, 6).Copy
              Sheets(&quot;SQUARETUBE&quot;).Range(&quot;Q&quot; &amp; Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
          End If
      Next i
      Worksheets(&quot;CALCULATIONS&quot;).Range(&quot;N24&quot;).Value = Worksheets(&quot;SQUARETUBE&quot;).Range(&quot;Q2&quot;).Value
  Application.ScreenUpdating = True
  End If
  
  
  If ThisWorkbook.Sheets(&quot;SHEET1&quot;).Range(&quot;E4&quot;) = &quot;TUBE_ROUND&quot; And ThisWorkbook.Sheets(&quot;SHEET1&quot;).Range(&quot;F4&quot;) &lt;&gt; 0 Then
  
  
  Application.ScreenUpdating = False
  Sheets(&quot;ROUNDTUBE&quot;).Range(&quot;Q2:Q100&quot;).ClearContents
  OD = Sheets(&quot;SHEET1&quot;).Range(&quot;F4&quot;).Value
  WALL1 = Sheets(&quot;SHEET1&quot;).Range(&quot;H4&quot;).Value
  FINALROW = Sheets(&quot;ROUNDTUBE&quot;).Cells(Rows.Count, 3).End(xlUp).Row
  
      For i = 2 To FINALROW
          If Worksheets(&quot;ROUNDTUBE&quot;).Cells(i, 3) = OD And Worksheets(&quot;ROUNDTUBE&quot;).Cells(i, 4) = WALL1 Then
              Worksheets(&quot;ROUNDTUBE&quot;).Cells(i, 5).Copy
              Sheets(&quot;ROUNDTUBE&quot;).Range(&quot;Q&quot; &amp; Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
          End If
      Next i
      Worksheets(&quot;CALCULATIONS&quot;).Range(&quot;N24&quot;).Value = Worksheets(&quot;ROUNDTUBE&quot;).Range(&quot;Q2&quot;).Value
  Application.ScreenUpdating = True
  End If
  
  
  If ThisWorkbook.Sheets(&quot;SHEET1&quot;).Range(&quot;E4&quot;) = &quot;PIPE&quot; And ThisWorkbook.Sheets(&quot;SHEET1&quot;).Range(&quot;F4&quot;) &lt;&gt; 0 Then
  
  
  Application.ScreenUpdating = False
  Sheets(&quot;PIPE&quot;).Range(&quot;Q2:Q100&quot;).ClearContents
  OD = Sheets(&quot;SHEET1&quot;).Range(&quot;F4&quot;).Value
  WALL1 = Sheets(&quot;SHEET1&quot;).Range(&quot;H4&quot;).Value
  FINALROW = Sheets(&quot;PIPE&quot;).Cells(Rows.Count, 3).End(xlUp).Row
  
      For i = 2 To FINALROW
          If Worksheets(&quot;PIPE&quot;).Cells(i, 3) = OD And Worksheets(&quot;PIPE&quot;).Cells(i, 4) = WALL1 Then
              Worksheets(&quot;PIPE&quot;).Cells(i, 5).Copy
              Sheets(&quot;PIPE&quot;).Range(&quot;Q&quot; &amp; Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
          End If
      Next i
      Worksheets(&quot;CALCULATIONS&quot;).Range(&quot;N24&quot;).Value = Worksheets(&quot;PIPE&quot;).Range(&quot;Q2&quot;).Value
  Application.ScreenUpdating = True
  End If
  
  
  If ThisWorkbook.Sheets(&quot;SHEET1&quot;).Range(&quot;E4&quot;) = &quot;SOLID_ROUND&quot; And ThisWorkbook.Sheets(&quot;SHEET1&quot;).Range(&quot;F4&quot;) &lt;&gt; 0 Then
  
  
  Application.ScreenUpdating = False
  Sheets(&quot;ROUND&quot;).Range(&quot;Q2:Q100&quot;).ClearContents
  OD = Sheets(&quot;SHEET1&quot;).Range(&quot;F4&quot;).Value
  FINALROW = Sheets(&quot;ROUND&quot;).Cells(Rows.Count, 3).End(xlUp).Row
  
      For i = 2 To FINALROW
          If Worksheets(&quot;ROUND&quot;).Cells(i, 3) = OD Then
              Worksheets(&quot;ROUND&quot;).Cells(i, 4).Copy
              Sheets(&quot;ROUND&quot;).Range(&quot;Q&quot; &amp; Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
          End If
      Next i
      Worksheets(&quot;CALCULATIONS&quot;).Range(&quot;N24&quot;).Value = Worksheets(&quot;ROUND&quot;).Range(&quot;Q2&quot;).Value
  Application.ScreenUpdating = True
  End If
  
  
  If ThisWorkbook.Sheets(&quot;SHEET1&quot;).Range(&quot;E4&quot;) = &quot;SOLID_FLAT&quot; And ThisWorkbook.Sheets(&quot;SHEET1&quot;).Range(&quot;F4&quot;) &lt;&gt; 0 Then
  
  
  Application.ScreenUpdating = False
  Sheets(&quot;FLAT&quot;).Range(&quot;Q2:Q100&quot;).ClearContents
  THICKNESS = Sheets(&quot;SHEET1&quot;).Range(&quot;F4&quot;).Value
  WIDTH = Sheets(&quot;SHEET1&quot;).Range(&quot;G4&quot;).Value
  FINALROW = Sheets(&quot;FLAT&quot;).Cells(Rows.Count, 3).End(xlUp).Row
  
      For i = 2 To FINALROW
          If Worksheets(&quot;FLAT&quot;).Cells(i, 3) = THICKNESS And Worksheets(&quot;FLAT&quot;).Cells(i, 4) = WIDTH Then
              Worksheets(&quot;FLAT&quot;).Cells(i, 5).Copy
              Sheets(&quot;FLAT&quot;).Range(&quot;Q&quot; &amp; Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
          End If
      Next i
      Worksheets(&quot;CALCULATIONS&quot;).Range(&quot;N24&quot;).Value = Worksheets(&quot;FLAT&quot;).Range(&quot;Q2&quot;).Value
  Application.ScreenUpdating = True
  End If
  
  
  If ThisWorkbook.Sheets(&quot;SHEET1&quot;).Range(&quot;E4&quot;) = &quot;SOLID_SQUARE&quot; And ThisWorkbook.Sheets(&quot;SHEET1&quot;).Range(&quot;F4&quot;) &lt;&gt; 0 Then
  
  
  Application.ScreenUpdating = False
  Sheets(&quot;SQUARE&quot;).Range(&quot;Q2:Q100&quot;).ClearContents
  WIDTH = Sheets(&quot;SHEET1&quot;).Range(&quot;F4&quot;).Value
  FINALROW = Sheets(&quot;SQUARE&quot;).Cells(Rows.Count, 3).End(xlUp).Row
  
      For i = 2 To FINALROW
          If Worksheets(&quot;SQUARE&quot;).Cells(i, 3) = WIDTH Then
              Worksheets(&quot;SQUARE&quot;).Cells(i, 4).Copy
              Sheets(&quot;SQUARE&quot;).Range(&quot;Q&quot; &amp; Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
          End If
      Next i
      Worksheets(&quot;CALCULATIONS&quot;).Range(&quot;N24&quot;).Value = Worksheets(&quot;SQUARE&quot;).Range(&quot;Q2&quot;).Value
  Application.ScreenUpdating = True
  End If
  
  
  If ThisWorkbook.Sheets(&quot;SHEET1&quot;).Range(&quot;E4&quot;) = &quot;SOLID_HEX&quot; And ThisWorkbook.Sheets(&quot;SHEET1&quot;).Range(&quot;F4&quot;) &lt;&gt; 0 Then
  
  
  Application.ScreenUpdating = False
  Sheets(&quot;HEX&quot;).Range(&quot;Q2:Q100&quot;).ClearContents
  WIDTH = Sheets(&quot;SHEET1&quot;).Range(&quot;F4&quot;).Value
  FINALROW = Sheets(&quot;HEX&quot;).Cells(Rows.Count, 3).End(xlUp).Row
  
      For i = 2 To FINALROW
          If Worksheets(&quot;HEX&quot;).Cells(i, 3) = WIDTH Then
              Worksheets(&quot;HEX&quot;).Cells(i, 4).Copy
              Sheets(&quot;HEX&quot;).Range(&quot;Q&quot; &amp; Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
          End If
      Next i
      Worksheets(&quot;CALCULATIONS&quot;).Range(&quot;N24&quot;).Value = Worksheets(&quot;HEX&quot;).Range(&quot;Q2&quot;).Value
      Worksheets(&quot;CALCULATIONS&quot;).Range(&quot;N25&quot;).Value = Worksheets(&quot;CALCULATIONS&quot;).Range(&quot;N8&quot;).Value / 12 * Worksheets(&quot;CALCULATIONS&quot;).Range(&quot;N24&quot;).Value
      Worksheets(&quot;CALCULATIONS&quot;).Range(&quot;N26&quot;).Value = Worksheets(&quot;CALCULATIONS&quot;).Range(&quot;N25&quot;).Value - ((Worksheets(&quot;CALCULATIONS&quot;).Range(&quot;N6&quot;).Value * Worksheets(&quot;CALCULATIONS&quot;).Range(&quot;N10&quot;).Value / 12) * Worksheets(&quot;CALCULATIONS&quot;).Range(&quot;N24&quot;).Value)
  Application.ScreenUpdating = True
  End If
  
  
  End Sub
  </code></pre>