Results 1 to 13 of 13
  1. #1
    4 Star Lounger
    Join Date
    May 2002
    Location
    US
    Posts
    439
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Compare list to Column (2000 sp3)

    All,
    I have several spreadsheets tha all contain basically the same data. I have a static list that I want to compare to the workbook, sheet by sheet. What I need to do is have the static list, run a macro to compare column A to the static list and if a match is found, highlight cell Yellow.

    Any Thoughts?

    Brad

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Compare list to Column (2000 sp3)

    Since I did not have your workbooks, this code has not been tested:

    <pre>Public Sub HLMatch()
    Dim oWB As Workbook, oWS As Worksheet, oCell As Range, oCellList As Range
    Dim oLS As Worksheet
    Dim vFile As Variant
    Set oLS = ThisWorkbook.Worksheets("Sheet1")
    vFile = Application.GetOpenFilename("Excel (*.xls), *.xls, All files (*.*), *.*")
    If Not vFile Then Exit Sub
    oWB = Workbooks.Open(vFile)
    For Each oWS In oWB.Worksheets
    For Each oCell In oWS.Range(oWS.Range("A1"), oWS.Range("A65536").End(xlUp))
    For Each oCellList In oLS.Range(oLS.Range("A1"), oLS.Range("A65536").End(xlUp))
    If oCell.Value = oCell.ListHeaderRows Then
    oCell.Interior.ColorIndex = 6
    Exit For
    End If
    Next oCellList
    Next oCell
    Next oWS
    End Sub
    </pre>

    Legare Coleman

  3. #3
    4 Star Lounger
    Join Date
    May 2002
    Location
    US
    Posts
    439
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Compare list to Column (2000 sp3)

    Legare,
    I was just able to get into the lounge, and found your below reply. First, Thank!. Second, I am unable to send you the actual file, as it contains proprietary information. However when I ran the code it stops at "If Not vFile Then Exit Sub" where the debug highlights the "IF Not vFile Then" It does open the file. But stops at this point.

    Any ideas?
    Brad

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Compare list to Column (2000 sp3)

    Try

    If vFile = False Then Exit Sub

  5. #5
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Compare list to Column (2000 sp3)

    <P ID="edit" class=small>(Edited by Rudi on 20-Jun-05 17:13. Tested now....and Legare's line of code it correct. No End If needed if structure over 1 line as in his code!)</P>I speak under correction, but with a quick sqizz through the code, I pick up that there is no matching "End If" for the If Not vFile Then Exit Sub line!

    Just my <img src=/S/2cents.gif border=0 alt=2cents width=15 height=15> without testing my comments!
    Regards,
    Rudi

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Compare list to Column (2000 sp3)

    Since the Exit Sub is on the same line as If Not vFile Then, no End Sub is needed. There are two forms:

    If condition Then
    someaction
    End If

    and

    If condition Then someaction

    The equivalents including an Else part are

    If condition Then
    someaction
    Else
    otheraction
    End If

    and

    If condition Then someaction Else otheraction

  7. #7
    4 Star Lounger
    Join Date
    May 2002
    Location
    US
    Posts
    439
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Compare list to Column (2000 sp3)

    Hans, (all)
    That got me past that line. Now the cod hangs at "oWB = Workbooks.Open(vFile)" Error message : Object Variable or With block variable not set.


    Regards,
    Brad

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Compare list to Column (2000 sp3)

    Change it to

    Set oWB = Workbooks.Open(vFile)

    A workbook is an object, so it must be assigned using Set.

  9. #9
    4 Star Lounger
    Join Date
    May 2002
    Location
    US
    Posts
    439
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Compare list to Column (2000 sp3)

    Hans,
    Is the assumption that the 'static' list is one file, , and that the code is in the static list VB, Then it compares the column A (static list) to only column A of the 'read' file? The code appears to run, but no changes are effected on the 'read' file.

    Regards,
    Brad

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Compare list to Column (2000 sp3)

    Does this work?

    Public Sub HLMatch()
    Dim oWB As Workbook, oWS As Worksheet, oCell As Range, oCellList As Range
    Dim oLS As Worksheet
    Dim vFile As Variant
    Set oLS = ThisWorkbook.Worksheets(1)
    vFile = Application.GetOpenFilename("Excel (*.xls), *.xls, All files (*.*), *.*")
    If vFile = False Then Exit Sub
    Set oWB = Workbooks.Open(vFile)
    For Each oWS In oWB.Worksheets
    For Each oCell In oWS.Range(oWS.Range("A1"), oWS.Range("A65536").End(xlUp))
    Set oCellList = oLS.Range("A:A").Find _
    (What:=oCell.Value, LookIn:=xlValues, _
    LookAt:=xlWhole, MatchCase:=False)
    If Not oCellList Is Nothing Then
    oCell.Interior.ColorIndex = 6
    End If
    Next oCell
    Next oWS
    End Sub

    It is to be run from the workbook containing the static list in column A in its first worksheet.

  11. #11
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Compare list to Column (2000 sp3)

    There is another bug in my "air code". Try this:

    Public Sub HLMatch()
    Dim oWB As Workbook, oWS As Worksheet, oCell As Range, oCellList As Range
    Dim oLS As Worksheet
    Dim vFile As Variant
    Set oLS = ThisWorkbook.Worksheets("Sheet1")
    vFile = Application.GetOpenFilename("Excel (*.xls), *.xls, All files (*.*), *.*")
    If vFile = False Then Exit Sub
    Set oWB = Workbooks.Open(vFile)
    For Each oWS In oWB.Worksheets
    For Each oCell In oWS.Range(oWS.Range("A1"), oWS.Range("A65536").End(xlUp))
    For Each oCellList In oLS.Range(oLS.Range("A1"), oLS.Range("A65536").End(xlUp))
    If oCell.Value = oCellList.Value Then
    oCell.Interior.ColorIndex = 6
    Exit For
    End If
    Next oCellList
    Next oCell
    Next oWS
    End Sub
    Legare Coleman

  12. #12
    4 Star Lounger
    Join Date
    May 2002
    Location
    US
    Posts
    439
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Compare list to Column (2000 sp3)

    Legare (and all!)
    Thanks, this worked great. Given that I have to look ad thousands of 'bit's of data' this helps greatly,

    Brad

  13. #13
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Compare list to Column (2000 sp3)

    Glad to help. Sorry about the bugs in the "air code".
    Legare Coleman

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •