Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pasting Word Text - strange results | Excel Discussion (Misc queries) | |||
Simple subtraction formula returning strange results = Excel glitc | Excel Worksheet Functions | |||
VLOOKUP formula results strange after copying down | Excel Worksheet Functions | |||
Database Functions - Strange results | Excel Worksheet Functions | |||
Strange Results with Autofilter | Excel Discussion (Misc queries) |