Trying to find a value from one sheet, on another, then compare rows

[Phillips]

I am tring to fing an email address from one sheet, on another
("NewMaster".)
email is in col 5
date is in col 10

Than, if the email is found on "NewMaster" compare dates. If the date is
NEWER, than copy the data that is in "NewMaster", to "DUPS", and then
replace the data in "NewMaster" from the excel sheet I am testing from.
If it is OLDER, than copy info to DUPS.
If NOT found, than ADD it to "NewMaster".

I am having problems just FINDING the email.... Here is what I have...

HELP,
Phil


Public Function CompRows(passemail As String) ', ws1 As String, ws2 As
String)
Dim nWS As String
Dim C
Dim successfull_activate
nWS = "NewMaster"
'MsgBox passemail
Windows("Testq.xls").Activate
' MsgBox "Select: " &
Sheets("NewMaster").Select
'
' Windows("Testq.xls").Activate
' Activate nWS
On Error GoTo Err_Handler
'MsgBox "Select: " & Worksheets("NewMaster").Activate
'MsgBox
'Set successfull_activate = Worksheets("NewMaster").Activate
Cells.Select
Set C = Selection.Find(What:=passemail, After:=ActiveCell, LookIn _
:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=
_
xlNext, MatchCase:=False, SearchFormat:=False).Activate
' MsgBox C

'Activate
Cells.Select


' MsgBox Cells.Find(What:=(passemail)).Activate
' MsgBox "value:" & ActiveCell.Value
If ActiveCell.Value = (passemail) Then
'***** Excel found a match
MsgBox "Found"
Else
'****** Excel Didn't find a match,
'****** so move to next sheet or stop searching
MsgBox "Not Found"
End If

Err_Handler:
'Error handling code

Select Case Err.Number
Case 91 '***** Error # when excel Find can't find value in Sheet
Resume Next
Case Else
End Select




End Function

 

[Tom Ogilvy]

If you are trying to use the function in a worksheet
as in =Comprow(emailaddress)

then find doesn't work in a UDF in xl2000 and earlier.

Also, a function can only return a value to the cell in which it is
located - it can't change values or formatting in other cells.

 

[Phillips]

I am planing on calling this from a for loop, stepping through each record
in my old worksheet
something like

sub compdata
dim mCount = worksheets("master").count
for i = 1 to mcount
mTest = activecell.offset(i,5)
mComp = Comprow(mTest)
activecell.offset(i, 48) = mComp ' what happeded- was it updated, a
duplicate or added
next
end sub


Thanks,
Phil

 

[Tom Ogilvy]

This will return the cell in column 5 that contains the email address:

Public Function CompRows(passemail As String) as Range
Dim nWS As String
Dim C as Range
Dim rng as Range

nWS = "NewMaster"
Set rng = Workbooks("Testq.xls"). _
worksheets(nWS).Columns(5).Cells
On Error GoTo Err_Handler

Set c = rng.Find(What:=passemail, _
After:=rng(1), LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)


set CompRows = c
End Function



so you would call it as

Dim mComp as Range, dDate as Date

set mComp = CompRows(mTest)
if mComp is nothing then
' email was not found, copy data to newmaster
Set rng = Workbooks("Testq.xls"). _
worksheets(nWS).Cells(rows.count,1).End(xlup) _
.Offset(1,0)
activeCell.EntireRow.copy Destination:= _
rng
else
dDate = mComp.Offset(0,5).Value
if dDate > ActiveCell.Offset(0,5).Value then
' copy ActiveCell to dups
ActiveCell.EntireRow.Copy Destination:= _
Worksheets("Dups").Cells(rows.count,1).End(xlup).Offset(1,0)
else
' copy newmaster data to dups
mComp.EntireRow.Copy Destination:= _
Worksheets("Dups").Cells(rows.count,1).End(xlup).Offset(1,0)
' copy this row to newmaster
ActiveCell.EntireRow.copy Destination:= _
mComp.EntireRow
end if
end if