title: If cell value is #NV get another cell
body: <p>I've got the following code to work with</p>
  
  <pre><code>    Set sourcerange = Sheets("Sheet1").Range("AW2:AW" &amp; lastRow)
      Set resultrange = Sheets("Sheet2").Range("A2:H" &amp; lastRow)
  
      i = 1
  
  
      For Each cell In sourcerange
          If cell.Value = 1 Then
              resultrange.Rows.Cells(i, 1).Value = Sheets("Sheet1").Range("O" &amp; cell.row).Value
              resultrange.Rows.Cells(i, 2).Value = Sheets("Sheet1").Range("X" &amp; cell.row).Value
              resultrange.Rows.Cells(i, 3).Value = Sheets("Sheet1").Range("P" &amp; cell.row).Value
              resultrange.Rows.Cells(i, 4).Value = Sheets("Sheet1").Range("J" &amp; cell.row).Value
              resultrange.Rows.Cells(i, 5).Value = Sheets("Sheet1").Range("K" &amp; cell.row).Value
              resultrange.Rows.Cells(i, 6).Value = Sheets("Sheet1").Range("G" &amp; cell.row).Value
              resultrange.Rows.Cells(i, 7).Value = Sheets("Sheet1").Range("H" &amp; cell.row).Value
              resultrange.Rows.Cells(i, 8).Value = Sheets("Sheet1").Range("I" &amp; cell.row).Value
              i = i + 1
          End If
  Next cell
  </code></pre>
  
  <p>Now to my problem:
  Sometimes it happens, that the value from this code:</p>
  
  <pre><code>resultrange.Rows.Cells(i, 2).Value = Sheets("Sheet1").Range("X" &amp; cell.row).Value
  </code></pre>
  
  <p>is #NV. In this case, I want to copy the value from the column "AA". But I've got no idea how to accomplish this. Is there a way to add something in my code to bring it to work? Or do I have to rewrite everything?</p>