Move values
 

What can be wrong?

Sub copy()
Dim rng As Range, cell As Range
Dim rw As Long
Dim cLastRow As Long

On Error Resume Next
Set rng = Range("G3:G4510").SpecialCells(xlFormulas, xlErrors)
On Error GoTo 0
rw = 0
If Not rng Is Nothing Then
For Each cell In rng
If cell.Text = "#N/A" Then
rw = rw + 1
End If


Sheets("Sheet1").Select
cLastRow = Cells(Rows.Count, "C").End(xlUp).Row
Range("C" & cLastRow).Offset(1, 0).Select


Sheets("Sheet1").Cells(rw, "C").Value =
Sheets("Sheet2").Cells(cell.Row, "A").Value


Next
End If
End Sub

Move values
 

I don't see a reason for this portion:

Sheets("Sheet1").Select
cLastRow = Cells(Rows.Count, "C").End(xlUp).Row
Range("C" & cLastRow).Offset(1, 0).Select

(I'd comment it out and see if made any difference.)

And you only add to the rw variable when you have an error that evaluates to
#N/A. What about #value!, #ref!, etc?

But I'm not sure what you're really asking for. You may want to be more
explicit with what you want and what isn't working.

Geir wrote:

What can be wrong?

Sub copy()
Dim rng As Range, cell As Range
Dim rw As Long
Dim cLastRow As Long

On Error Resume Next
Set rng = Range("G3:G4510").SpecialCells(xlFormulas, xlErrors)
On Error GoTo 0
rw = 0
If Not rng Is Nothing Then
For Each cell In rng
If cell.Text = "#N/A" Then
rw = rw + 1
End If


Sheets("Sheet1").Select
cLastRow = Cells(Rows.Count, "C").End(xlUp).Row
Range("C" & cLastRow).Offset(1, 0).Select


Sheets("Sheet1").Cells(rw, "C").Value =
Sheets("Sheet2").Cells(cell.Row, "A").Value


Next
End If
End Sub

--

Dave Peterson