title: Run time error 1004. Method 'Range' object '_Global' _ failed
body: <p>When I run my program only for one line (instead of variable i, put any other number) of the worksheet, there's no problem. But when I tried to do it for other lines using a FOR loop, the error message  appears <code>Run time error 1004. Method 'Range' object '_Global' _ failed</code>. The debugger always highlights the 14th line. But I don't know if I have to fix just this line. Hope anyone can help me...my code is below.</p>
  
  <pre><code>Sub depth()
      Dim VAR1 As Double
      Dim VAR2 As Double
      Dim VAR3 As Double
      Dim finalDepth As Double
      Dim i As Integer
  
    ' Calculation of depth when soil temperature is zero. Calculation 
    ' was made considering linear change of temperature between 2 values
  
      With ThisWorkbook.Worksheets("sheet1")
          For i = 2 To 30547
  
            ' (PART HIGHLIGHTED BY DEBUGGER)
  
              If Range("Fi").Value &gt; 0 And _
                  Range("Gi").Value &gt; 0 And _
                  Range("Hi").Value &gt; 0 And _
                  Range("Ii").Value &gt; 0 And _
                  Range("Ji").Value &gt; 0 And _
                  Range("Ki").Value &gt; 0 And _
                  Range("Li").Value &gt; 0 And _
                  Range("Mi").Value &gt; 0 And _
                  Range("Ni").Value &gt; 0 And _
                  Range("Oi").Value &gt; 0 And _
                  Range("Pi").Value &gt; 0 Or _
                  Range("Fi").Value &lt; 0 And _
                  Range("Gi").Value &lt; 0 And _
                  Range("Hi").Value &lt; 0 And _
                  Range("Ii").Value &lt; 0 And _
                  Range("Ji").Value &lt; 0 And _
                  Range("Ki").Value &lt; 0 And _
                  Range("Li").Value &lt; 0 And _
                  Range("Mi").Value &lt; 0 And _
                  Range("Ni").Value &lt; 0 And _
                  Range("Oi").Value &lt; 0 And _
                  Range("Pi").Value &lt; 0 Then
  
                  Range("Ri").Value = 0
  
            ' Calculation of depth when temperature is zero for each column on one row
  
              ElseIf (Range("Fi").Value &gt; 0 And Range("Gi").Value &lt; 0) Then
                  VAR1 = (Range("Fi") - Range("Gi"))
                  VAR2 = VAR1 / (Range("G1") - Range("F1"))
                  VAR3 = Range("Fi") / VAR2
                  finalDepth = Range("F1") + VAR3
                  Range("Ri").Value = finalDepth
  
            ' Columns g and h
  
              ElseIf (Range("Gi").Value &gt; 0 And Range("Hi").Value &lt; 0) Then
                  VAR1 = (Range("Gi") - Range("Hi"))
                  VAR2 = VAR1 / (Range("H1") - Range("G1"))
                  VAR3 = Range("Gi") / VAR2
                  finalDepth = Range("G1") + VAR3
                  Range("Ri").Value = finalDepth
  
            ' Columns h and i
  
              ElseIf (Range("Hi").Value &gt; 0 And Range("Ii").Value &lt; 0) Then
                  VAR1 = (Range("Hi") - Range("Ii"))
                  VAR2 = VAR1 / (Range("I1") - Range("H1"))
                  VAR3 = Range("Hi") / VAR2
                  finalDepth = Range("H1") + VAR3
                  Range("Ri").Value = finalDepth
  
            ' Columns i and j
  
              ElseIf (Range("Ii").Value &gt; 0 And Range("Ji").Value &lt; 0) Then
                  VAR1 = (Range("Ii") - Range("Ji"))
                  VAR2 = VAR1 / (Range("J1") - Range("I1"))
                  VAR3 = Range("Ii") / VAR2
                  finalDepth = Range("I1") + VAR3
                  Range("Ri").Value = finalDepth
  
            ' Columns j and k
  
              ElseIf (Range("Ji").Value &gt; 0 And Range("Ki").Value &lt; 0) Then
                  VAR1 = (Range("Ji") - Range("Ki"))
                  VAR2 = VAR1 / (Range("K1") - Range("J1"))
                  VAR3 = Range("Ji") / VAR2
                  finalDepth = Range("J1") + VAR3
                  Range("Ri").Value = finalDepth
  
            ' Columns k and l
  
              ElseIf (Range("Ki").Value &gt; 0 And Range("Li").Value &lt; 0) Then
                  VAR1 = (Range("Ki") - Range("Li"))
                  VAR2 = VAR1 / (Range("L1") - Range("K1"))
                  VAR3 = Range("Ki") / VAR2
                  finalDepth = Range("K1") + VAR3
                  Range("Ri").Value = finalDepth
  
            ' Columns l and m
  
              ElseIf (Range("Li").Value &gt; 0 And Range("Mi").Value &lt; 0) Then
                  VAR1 = (Range("Li") - Range("Mi"))
                  VAR2 = VAR1 / (Range("M1") - Range("L1"))
                  VAR3 = Range("Li") / VAR2
                  finalDepth = Range("L1") + VAR3
                  Range("Ri").Value = finalDepth
  
            ' Columns m and n
  
              ElseIf (Range("Mi").Value &gt; 0 And Range("Ni").Value &lt; 0) Then
                  VAR1 = (Range("Mi") - Range("Ni"))
                  VAR2 = VAR1 / (Range("N1") - Range("M1"))
                  VAR3 = Range("Mi") / VAR2
                  finalDepth = Range("M1") + VAR3
                  Range("Ri").Value = finalDepth
  
            ' Columns n and o
  
              ElseIf (Range("Ni").Value &gt; 0 And Range("Oi").Value &lt; 0) Then
                  VAR1 = (Range("Ni") - Range("Oi"))
                  VAR2 = VAR1 / (Range("O1") - Range("N1"))
                  VAR3 = Range("Ni") / VAR2
                  finalDepth = Range("N1") + VAR3
                  Range("Ri").Value = finalDepth
  
            ' Column o and p
  
              ElseIf (Range("Oi").Value &gt; 0 And Range("Pi").Value &lt; 0) Then
                  VAR1 = (Range("Oi") - Range("Pi"))
                  VAR2 = VAR1 / (Range("P1") - Range("O1"))
                  VAR3 = Range("Oi") / VAR2
                  finalDepth = Range("O1") + VAR3
                  Range("Ri").Value = finalDepth
              End If
          Next i
      End With
  End Sub
  </code></pre>