HELP PLEASE: trying to compare the result of a cell formula in Cells(A, "C") its contains the result of a Vlookup, I am trying to look for < with another cell Cells(A, "E") the macro runs once then locks up error code 2042. Anyone any ideas please? Or better code !!!

Sub CellCompare()
Dim A As Integer
A = 1
Sheets("NIMROD001").Select
Do Until IsEmpty(Cells(A, "B"))
If Cells(A, "C") < Cells(A, "E") Then ' Doesnt like formulae
Rows(A).Copy
Sheets("Sheet1").Select
Cells(ActiveCell.Row + 1, 1).PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Sheets("NIMROD001").Select
End If
A = A + 1
Loop
End Sub

Les,

I have just tested this and it works for me.

What does your data look like, and what description does 2042 give?

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Les" wrote in message ...
HELP PLEASE: trying to compare the result of a cell formula in Cells(A, "C") its contains the result of a Vlookup, I am trying to look for < with another cell Cells(A, "E") the macro runs once then locks up error code 2042. Anyone any ideas please? Or better code !!!

Sub CellCompare()
Dim A As Integer
A = 1
Sheets("NIMROD001").Select
Do Until IsEmpty(Cells(A, "B"))
If Cells(A, "C") < Cells(A, "E") Then ' Doesnt like formulae
Rows(A).Copy
Sheets("Sheet1").Select
Cells(ActiveCell.Row + 1, 1).PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Sheets("NIMROD001").Select
End If
A = A + 1
Loop
End Sub

Thanks for the response....

Run-time error '13':
Type mismatch

The macro works fine for the first loop then gives me the error.
"Bob Phillips" wrote in message ...
Les,

I have just tested this and it works for me.

What does your data look like, and what description does 2042 give?

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Les" wrote in message ...
HELP PLEASE: trying to compare the result of a cell formula in Cells(A, "C") its contains the result of a Vlookup, I am trying to look for < with another cell Cells(A, "E") the macro runs once then locks up error code 2042. Anyone any ideas please? Or better code !!!

Sub CellCompare()
Dim A As Integer
A = 1
Sheets("NIMROD001").Select
Do Until IsEmpty(Cells(A, "B"))
If Cells(A, "C") < Cells(A, "E") Then ' Doesnt like formulae
Rows(A).Copy
Sheets("Sheet1").Select
Cells(ActiveCell.Row + 1, 1).PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Sheets("NIMROD001").Select
End If
A = A + 1
Loop
End Sub

One of the cell has this formula in it =VLOOKUP(D7,'Looms from PPAS'!$A$1:$B$1308,2,FALSE)

The other cell just GENERAL alpha numerics...

Spent hours trying to sort this.... very demoralising!!!
"Les" wrote in message ...
Thanks for the response....

Run-time error '13':
Type mismatch

The macro works fine for the first loop then gives me the error.
"Bob Phillips" wrote in message ...
Les,

I have just tested this and it works for me.

What does your data look like, and what description does 2042 give?

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Les" wrote in message ...
HELP PLEASE: trying to compare the result of a cell formula in Cells(A, "C") its contains the result of a Vlookup, I am trying to look for < with another cell Cells(A, "E") the macro runs once then locks up error code 2042. Anyone any ideas please? Or better code !!!

Sub CellCompare()
Dim A As Integer
A = 1
Sheets("NIMROD001").Select
Do Until IsEmpty(Cells(A, "B"))
If Cells(A, "C") < Cells(A, "E") Then ' Doesnt like formulae
Rows(A).Copy
Sheets("Sheet1").Select
Cells(ActiveCell.Row + 1, 1).PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Sheets("NIMROD001").Select
End If
A = A + 1
Loop
End Sub

Do you any of your cells have an error value (E.g., #VALUE, #NA,
etc)? If so, you'll get a type mismatch error. Try testing for an
error value

Do Until IsEmpty(Cells(A, "B"))
If IsError(Cells(A, "C")) = False And IsError(Cells(A, "E"))
= False Then
If Cells(A, "C") < Cells(A, "E") Then ' Doesnt like
formulae
Rows(A).Copy
Sheets("Sheet1").Select
Cells(ActiveCell.Row + 1, 1).PasteSpecial
Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Sheets("NIMROD001").Select
End If
A = A + 1
Loop



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com





"Les" wrote in message
...
HELP PLEASE: trying to compare the result of a cell formula in
Cells(A, "C") its contains the result of a Vlookup, I am trying
to look for < with another cell Cells(A, "E") the macro runs
once then locks up error code 2042. Anyone any ideas please? Or
better code !!!

Sub CellCompare()
Dim A As Integer
A = 1
Sheets("NIMROD001").Select
Do Until IsEmpty(Cells(A, "B"))
If Cells(A, "C") < Cells(A, "E") Then ' Doesnt like formulae
Rows(A).Copy
Sheets("Sheet1").Select
Cells(ActiveCell.Row + 1, 1).PasteSpecial
Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Sheets("NIMROD001").Select
End If
A = A + 1
Loop
End Sub

try dim A as Long
iKKi
"Les" wrote in message ...
HELP PLEASE: trying to compare the result of a cell formula in Cells(A, "C") its contains the result of a Vlookup, I am trying to look for < with another cell Cells(A, "E") the macro runs once then locks up error code 2042. Anyone any ideas please? Or better code !!!

Sub CellCompare()
Dim A As Integer
A = 1
Sheets("NIMROD001").Select
Do Until IsEmpty(Cells(A, "B"))
If Cells(A, "C") < Cells(A, "E") Then ' Doesnt like formulae
Rows(A).Copy
Sheets("Sheet1").Select
Cells(ActiveCell.Row + 1, 1).PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Sheets("NIMROD001").Select
End If
A = A + 1
Loop
End Sub