Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trying to find a value from one sheet, on another, then compare rows
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trying to find a value from one sheet, on another, then compare rows
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. -- Regards, Tom Ogilvy "Phillips" wrote in message news:75Ayb.374520$Tr4.1113090@attbi_s03... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trying to find a value from one sheet, on another, then compare rows
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" wrote in message ... 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. -- Regards, Tom Ogilvy "Phillips" wrote in message news:75Ayb.374520$Tr4.1113090@attbi_s03... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trying to find a value from one sheet, on another, then compare rows
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).O ffset(1,0) else ' copy newmaster data to dups mComp.EntireRow.Copy Destination:= _ Worksheets("Dups").Cells(rows.count,1).End(xlup).O ffset(1,0) ' copy this row to newmaster ActiveCell.EntireRow.copy Destination:= _ mComp.EntireRow end if end if -- Regards, Tom Ogilvy "Phillips" wrote in message news:hNRyb.188557$Dw6.716434@attbi_s02... 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" wrote in message ... 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. -- Regards, Tom Ogilvy "Phillips" wrote in message news:75Ayb.374520$Tr4.1113090@attbi_s03... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find & delete rows with ID's from another sheet. | Excel Discussion (Misc queries) | |||
How to find a row in a sheet and compare the complete row /w origi | Excel Worksheet Functions | |||
Help me, compare 2 sheet and extract the match data into the new sheet. | Excel Discussion (Misc queries) | |||
Compare Sheet Cell to Sheet Column | Excel Worksheet Functions | |||
How do I find only the singular rows in a sheet with duplicates? | Excel Discussion (Misc queries) |