help with syntax for copy and pasting rows across sheets

[haileybury]

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.

 

[JLGWhiz]

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]

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]

When I used your code on my system, I used two worksheets. On Sheet 1,
Column A, I entered data for several cells. I left sheet 2 blank. Running
your code as originally written gave me a runtime error on the line If c <>
"" Then... for the Object variable not being set. The error occured because
c = Nothing and the variable could not be set to nothing. When I changed the
statement to evaluate against Nothing, it evaluated to False and executed the
copy statement. So that brings us to the question of what do you have on
sheet 2 that makes the statement evaluate to true. The way your Find
statement is written, you are checking the entire sheet 2. Perhaps you want
to narrow the area checked to a specific column or a smaller area of the
sheet. The only reason for it to evaluate as true is that it finds a match
for the data from sheet 1. Your code is otherwise sound.

 

[haileybury]

Thanks for your response.

I found the problem, the command button in which I had the macro written was
on sheet 2 and in the code I am setting the range to sheet1.
Due to this reason was setting the range reference to sheet2 instead of
sheet1 i.e it was comparing to itself hence it was failing for the condition
"if not c is nothing"

I found this when I was very frustrated and decided to swap the sheet names
and yahoo it worked fine... Still getting used to the way excel makes
references from where the macro is executed.

Thanks for your assistance in this matter....