Strange results using .FIND in Excel VBA
 

On a third worksheet put in a formula

=if(Countif(Attend!A:A,RSVP!A2)0,"",RSVP!A2)

in A2, then drag fill down the column.

Dress it up as required.

--
Regards,
Tom Ogilvy


"Liz Kegel" wrote in message
om...
Good afternoon... I am working on a series of macros to compare
multiple worksheets. I have everything working except for this piece.
I have two worksheets one for people who RSVPd and one for those who
actually ATTENDED (for a company sponsored event). Each person is
assigned a number. I want to find all of those people who RSVPd but
did not attend. I thought I had this working correctly but am missing
the boat somewhere. What should happen is I should have a new
worksheet with 2017 and 2007 in it. It appears I am not doing the
FIND on the correct worksheet and cannot figure out what I am doing
wrong. Could it be that the ranges are both in column A but on
different sheets? Any suggestions would be greatly appreciated.

RSVP ATTEND
2017 2004
2004 2006
2006
2007

Public Sub FIND_NOSHOWS()

Dim xRSVP As Integer 'row count for RSVP
Dim xAttend As Long 'row count for ATTEND
Dim myValue As String
Dim firstaddress As String
Dim rngAttend As Range
Dim rngRSVP As Range
Dim myCell As Range

Application.ScreenUpdating = True
intNoShow = 0


'set the range for those numbers that attended
With Worksheets("ATTEND")
xAttend = .Range("A2").End(xlDown).Row 'number of rows in employee
list
Set rngAttend = Range("A2:A" & xAttend)
End With

'set the range for those who RSVPd
With Worksheets("RSVP")
xRSVP = .Range("A2").End(xlDown).Row '#rows in first column
Set rngRSVP = Range("A2:A" & xRSVP)
End With

'Worksheets("ATTEND").Activate
With rngAttend 'look in the Attend sheet to see if the RSVPd or not
For Each myCell In rngRSVP
myValue = myCell.Value ' value is (2017)
iCount = iCount + 1
Set c = .Find(myValue, LookIn:=xlValues)
If Not c Is Nothing Then 'found a match
firstaddress = c.Address 'address is "$A$2"
which is for RSVP worksheet, not ATTEND like I want
Else
intNoShow = intNoShow + 1
xc = xc + 1
Worksheets("NOSHOWS").Activate
Worksheets("NOSHOWS").Cells(xc, "A").Value =
myValue
End If
Next
End With


End Sub