Loop Problem

[Todd Huttenstine]

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

 

[Bob Phillips]

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]

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) =

 

[Tom Ogilvy]

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

 

[JE McGimpsey]

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".

 

[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.

 

[Tom Ogilvy]

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.

 

[JE McGimpsey]

Todd,

remove the ":" after "A"

 

[Tom Ogilvy]

Good catch - I missed that.

 

[Todd Huttenstine]

Ah thanx that did it.


Appreciate everyones help.

Have a good day!

 

[Juan Pablo González]

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