Intra-workbook List Comparison

  • Hi,


    I have a chemical compounds database with 555 compounds in it, saved as Macros.xls in a worksheet named List . I need to make a macro that checks a new list in another workbook and highlights those compounds it found in the database.
    I have started a tentative code structure and almost got it working, except for the Find method inside the Find function. Can somebody help me?



    I am trying to search the database for each string in the new list (which is vertical in the A column of a new sheet), and am considering any range returned by the Find() function with value > 0 to be a positive match. Apart from that, however, I am in dire need for help.


    Can somebody come to my rescue?


    PS. Both the database and the new workbook are in the same folder.

  • Re: Intra-workbook List Comparison


    cramirez06,


    Without something to test the code by. Everything looks ok. However, your "Find" function doesn't return anything. So by default it will return FALSE.


    Try this "Find" function:

    Code
    Function Find(text As String, book As String, sheet As String) As Boolean 
        Dim found As Boolean 
        Find = FALSE
        If Workbooks(book).Worksheets(sheet).Cells.Find(What:=text, _ 
        LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _ 
        SearchDirection:=xlNext, MatchCase:=MatchCase, _ 
        SearchFormat:=False).Value > 0 Then Find = True 
    End Function
  • Re: Intra-workbook List Comparison


    IWRK:


    Thanks for responding to my post. I tried your find function, however, and I get a message:


    Error '9':


    Subscript out of range


    which highlights the IF method inside the function. Any idea why this might be?
    If you need a copy of the workbook, I would be glad to send it to you.

  • Re: Intra-workbook List Comparison


    Hi,


    The error 9 means that either the sheetname or workbook name you are using does not exist.
    I have tweaked the code slightly as even with the correct references if it does not find anything it would have errored.


    [vba]Function Find(text As String, book As String, sheet As String) As Boolean
    Dim objFind As Range
    Find = False
    Set objFind = Workbooks(book).Worksheets(sheet).Cells.Find(What:=text, _
    LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
    SearchDirection:=xlNext, MatchCase:=False, _
    SearchFormat:=False)
    If Not objFind Is Nothing Then Find = True
    End Function[/vba]

  • Re: Intra-workbook List Comparison


    cramirez06
    Junior Member


    Last night You contacted me direct by U2u, please do not do this for questions on the board, i can not assist everyone. As my sign says any such requsts will be ignored


    Good to see Your sorted, remember everyone has probelms and is in desperate need of help i can not just help on demand - thats just not OzGrid, i answer the questions i want to and for no other reasons just the same as everyone else


    Cheers buddy


    jiuk

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!