I tried the statement

"If Not c Is Nothing Then GoTo phred"

and the condition is always being evaluated as True and the program skips to
the label "phred" even when there is no matching value in sheet2 ( hence why
I was originally using If c<"" then goto phred" and it does not even give me
the error you mentioned)

I am running excel 2002 SP-2. Not sure if this is the issue...?


"JLGWhiz" wrote:

You should have gotten an error message that the Object Variable was not set
if the Find method returned nothing. I changed this line:

If c < "" Then GoTo phred

To :

If Not c Is Nothing Then GoTo phred

And it copied the data from sheet 1 to sheet 2.
If you are not using Sheet 3 for anything, you should delete the Dim line
and the variable assignment line to conserve memory.

"haileybury" wrote:

Following is the code I am using to check for a value in sheet1 against sheet
2. If value not found in sheet2 then I want to copy the value from sheet1 and
paste it at the bottom of the list of values in sheet2. What is not working
is the statement that I am using to copy the row from sheet 1 and pasting it
to sheet2

Cel.EntireRow.Copy destination:=Sht2.Cells(r, 1)


Following is the code

Private Sub CommandButton2_Click()

Dim r As Long 'Last row of data on Sheet 1 (row number)
Dim c As Range 'Range/counter
Dim Rng As Range 'Row 2 to row 'r' (above) as counter
Dim Cel As Range 'Cell value to find from Sheet1 on Sht2
Dim Sht1 As Worksheet 'Sheet1
Dim Sht2 As Worksheet 'Sheet2
Dim Sht3 As Worksheet 'Sheet3


Set Sht1 = Sheets("sheet1")
Set Sht2 = Sheets("sheet2")
Set Sht3 = Sheets("sheet3")

'Get last row Sheet 1, Column a
r = Sht1.Range("a65536").End(xlUp).Row

'Start with Sheet1
Sht1.Activate

'Set 'counter' as first row of data to last row of data (Col a)
Set Rng = Range(Cells(2, 1), Cells(r, 1))

'For cells first to last (counter)
For Each Cel In Rng
'find first Sheet1.Cell.Value in sheet 2
With Sht2.Cells
Set c = .Find(Cel, LookIn:=xlValues)
If c < "" Then GoTo phred
r = Sht2.Range("a65536").End(xlUp).Row + 1
Cel.EntireRow.Copy destination:=Sht2.Cells(r, 1) ' this is not
working...
End With
phred:
MsgBox "went to phred"
Next
End Sub

any tips or recommendations is much appreciated.