Loop Problem

T

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
 
B

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

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
Click to expand...
("Mismatches").Range("H2").Value) =
 
T

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
 
J

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

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

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

Juan Pablo González

Todd said:
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
Click to expand...

Another option is to use the ISNA() worksheet function

If Application.IsNa(cell1.Value) Then
 
Top