Dim rng As Range
Dim cell1 As Object
Set rng = Sheets("Converted Data").Range("O2:O100")
For Each cell1 In rng
If cell1 = "#N/A" Then
Sheets("Mismatches").Range("A:" & Sheets
("Mismatches").Range("H2").Value) = cell1
Else
End If
Next

I keep getting an error with the above code. It is not
seeing the #N/A error correctly. There are formulas all
in range O2:O100. How would I make this work?

Thank you

Todd Huttenstine

Hi Todd,

Try this

Dim rng As Range
Dim cell1 As Object
Set rng = Sheets("Converted Data").Range("O2:O100")
For Each cell1 In rng
If cell1.Value = CVErr(xlErrNA) Then
Sheets("Mismatches").Range("A:" & Sheets("Mismatches").Range("H2").Value) =
cell1
Else
End If
Next

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Todd Huttenstine" wrote in message
...
Dim rng As Range
Dim cell1 As Object
Set rng = Sheets("Converted Data").Range("O2:O100")
For Each cell1 In rng
If cell1 = "#N/A" Then
Sheets("Mismatches").Range("A:" & Sheets
("Mismatches").Range("H2").Value) = cell1
Else
End If
Next

I keep getting an error with the above code. It is not
seeing the #N/A error correctly. There are formulas all
in range O2:O100. How would I make this work?

Thank you

Todd Huttenstine

Still getting that error.



-----Original Message-----
Hi Todd,

Try this

Dim rng As Range
Dim cell1 As Object
Set rng = Sheets("Converted Data").Range("O2:O100")
For Each cell1 In rng
If cell1.Value = CVErr(xlErrNA) Then
Sheets("Mismatches").Range("A:" & Sheets
("Mismatches").Range("H2").Value) =
cell1
Else
End If
Next

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Todd Huttenstine"
wrote in message
...
Dim rng As Range
Dim cell1 As Object
Set rng = Sheets("Converted Data").Range("O2:O100")
For Each cell1 In rng
If cell1 = "#N/A" Then
Sheets("Mismatches").Range("A:" & Sheets
("Mismatches").Range("H2").Value) = cell1
Else
End If
Next

I keep getting an error with the above code. It is not
seeing the #N/A error correctly. There are formulas all
in range O2:O100. How would I make this work?

Thank you

Todd Huttenstine


.

Dim rng As Range
Dim cell1 As Object
Set rng = Sheets("Converted Data").Range("O2:O100")
For Each cell1 In rng
if iserror(cell1) then
If cell1.Text = "#N/A" Then
Sheets("Mismatches").Range("A:" & Sheets _
("Mismatches").Range("H2").Value) = cell1
End if
End If
Next

--
Regards,
Tom Ogilvy

"Todd Huttenstine" wrote in message
...
Dim rng As Range
Dim cell1 As Object
Set rng = Sheets("Converted Data").Range("O2:O100")
For Each cell1 In rng
If cell1 = "#N/A" Then
Sheets("Mismatches").Range("A:" & Sheets
("Mismatches").Range("H2").Value) = cell1
Else
End If
Next

I keep getting an error with the above code. It is not
seeing the #N/A error correctly. There are formulas all
in range O2:O100. How would I make this work?

Thank you

Todd Huttenstine

Well its looping through correctly, but then when it finds
a cell that equals the #N/A error, it hits the following
line of code...

Sheets("Mismatches").Range("A:" & Sheets
("Mismatches").Range("H2").Value) = cell1

I get the error Runtime error 1004 Application defined or
object defined error.



-----Original Message-----
Dim rng As Range
Dim cell1 As Object
Set rng = Sheets("Converted Data").Range("O2:O100")
For Each cell1 In rng
if iserror(cell1) then
If cell1.Text = "#N/A" Then
Sheets("Mismatches").Range("A:" & Sheets _
("Mismatches").Range("H2").Value) = cell1
End if
End If
Next

--
Regards,
Tom Ogilvy

"Todd Huttenstine"
wrote in message
...
Dim rng As Range
Dim cell1 As Object
Set rng = Sheets("Converted Data").Range("O2:O100")
For Each cell1 In rng
If cell1 = "#N/A" Then
Sheets("Mismatches").Range("A:" & Sheets
("Mismatches").Range("H2").Value) = cell1
Else
End If
Next

I keep getting an error with the above code. It is not
seeing the #N/A error correctly. There are formulas all
in range O2:O100. How would I make this work?

Thank you

Todd Huttenstine


.

Does H2 contain a value between 1 and 65536 inclusive?

It sounds like it doesn't.
Based on your code, I would guess that H2 contains a formula like

=CountA(A1:A65536)+1

if you want to progressively accumulate your values (although it seems like
all you would get would be the #N/A entered as text in the cell)

Which should always have at least a value of 1, but that would only be a
guess - since you are having problems, perhaps not.
--
Regards,
Tom Ogilvy


"Todd Huttenstine" wrote in message
...
Well its looping through correctly, but then when it finds
a cell that equals the #N/A error, it hits the following
line of code...

Sheets("Mismatches").Range("A:" & Sheets
("Mismatches").Range("H2").Value) = cell1

I get the error Runtime error 1004 Application defined or
object defined error.



-----Original Message-----
Dim rng As Range
Dim cell1 As Object
Set rng = Sheets("Converted Data").Range("O2:O100")
For Each cell1 In rng
if iserror(cell1) then
If cell1.Text = "#N/A" Then
Sheets("Mismatches").Range("A:" & Sheets _
("Mismatches").Range("H2").Value) = cell1
End if
End If
Next

--
Regards,
Tom Ogilvy

"Todd Huttenstine"
wrote in message
...
Dim rng As Range
Dim cell1 As Object
Set rng = Sheets("Converted Data").Range("O2:O100")
For Each cell1 In rng
If cell1 = "#N/A" Then
Sheets("Mismatches").Range("A:" & Sheets
("Mismatches").Range("H2").Value) = cell1
Else
End If
Next

I keep getting an error with the above code. It is not
seeing the #N/A error correctly. There are formulas all
in range O2:O100. How would I make this work?

Thank you

Todd Huttenstine


.

Todd,

remove the ":" after "A"


In article ,
"Todd Huttenstine" wrote:

Well its looping through correctly, but then when it finds
a cell that equals the #N/A error, it hits the following
line of code...

Sheets("Mismatches").Range("A:" & Sheets
("Mismatches").Range("H2").Value) = cell1

I get the error Runtime error 1004 Application defined or
object defined error.

Good catch - I missed that.

--
Regards,
Tom Ogilvy

"JE McGimpsey" wrote in message
...
Todd,

remove the ":" after "A"


In article ,
"Todd Huttenstine" wrote:

Well its looping through correctly, but then when it finds
a cell that equals the #N/A error, it hits the following
line of code...

Sheets("Mismatches").Range("A:" & Sheets
("Mismatches").Range("H2").Value) = cell1

I get the error Runtime error 1004 Application defined or
object defined error.

Ah thanx that did it.


Appreciate everyones help.

Have a good day!
-----Original Message-----
Todd,

remove the ":" after "A"


In article ,
"Todd Huttenstine"
wrote:

Well its looping through correctly, but then when it
finds
a cell that equals the #N/A error, it hits the
following
line of code...

Sheets("Mismatches").Range("A:" & Sheets
("Mismatches").Range("H2").Value) = cell1

I get the error Runtime error 1004 Application defined
or
object defined error.
.

Excel returns the Error (xlErrNA, or 2042) in the Value property if the
formula returns #N/A.

You could use:

If cell1.Text = "#N/A" Then

instead.


Todd - you've been posting here long enough to know that you should
include the error message that you get, rather than just saying "an
error".

In article ,
"Todd Huttenstine" wrote:

Dim rng As Range
Dim cell1 As Object
Set rng = Sheets("Converted Data").Range("O2:O100")
For Each cell1 In rng
If cell1 = "#N/A" Then
Sheets("Mismatches").Range("A:" & Sheets
("Mismatches").Range("H2").Value) = cell1
Else
End If
Next

I keep getting an error with the above code. It is not
seeing the #N/A error correctly. There are formulas all
in range O2:O100. How would I make this work?

Todd Huttenstine wrote:
Dim rng As Range
Dim cell1 As Object
Set rng = Sheets("Converted Data").Range("O2:O100")
For Each cell1 In rng
If cell1 = "#N/A" Then
Sheets("Mismatches").Range("A:" & Sheets
("Mismatches").Range("H2").Value) = cell1
Else
End If
Next

I keep getting an error with the above code. It is not
seeing the #N/A error correctly. There are formulas all
in range O2:O100. How would I make this work?

Thank you

Todd Huttenstine

Another option is to use the ISNA() worksheet function

If Application.IsNa(cell1.Value) Then

--
Regards,

Juan Pablo González