Comparing Data

Discussion in 'General Computer Support' started by IcePirate, Sep 5, 2008.

  1. IcePirate

    IcePirate

    Joined:
    Sep 5, 2008
    Messages:
    1
    Im working on a project that requires I VB code which Ive developed already, but this is how it works:

    We have 12 digit inventory numbers...There is one workbook and three sheets inside of it - sheet1 column A there is 2,000 lines of numbers; in sheet2, column A there is 30,000 lines of numbers. So on sheet three, I want to run a VB Script that takes the numbers from sheet1 and lists them in column A on sheet3, then I want the script to compare the 2,000 lines from sheet1, compare them to sheet2 to the 30,000 lines and in sheet 3, column A I want to display the numbers that are in both sheets, and not display the the numbers that aren't there...

    Or I would like the numbers in sheet1 to display in sheet3 with a yes, or no beside them (yes - meaning the number is in both sheets, no meaning the number is not there)

    Here is the full script any suggestions? (The script is currently not working)
    Code:
    Sub DupNumbers()
    Dim LR1, LR2, LR3 As Integer
    Dim x As Range
    Dim yesno As String
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationManual
    LR1 = Sheets(1).Range("A" & Rows.Count).End(xlUp).Row
    LR2 = Sheets(2).Range("A" & Rows.Count).End(xlUp).Row
    LR3 = Sheets(3).Range("A" & Rows.Count).End(xlUp).Row
    Sheets(1).Select
    For Each x In Range("A" & LR1)
        If IsError(Application.WorksheetFunction.Match(x, Sheets(2).Range("A1:A" & LR2), 0)) Then
            yesno = "No"
        Else
            yesno = "Yes"
        End If
        Sheets(3).Select
        LR3 = Range("A" & Rows.Count).End(xlUp).Row + 1
        Range("A" & LR3).Value = x.Value
        Range("B" & LR3).Value = yesno
    Next x
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    End Sub
    
     
    IcePirate, Sep 5, 2008
    #1
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. balroq@gmail.com
    Replies:
    3
    Views:
    6,330
    dt1649651@yahoo.com
    May 12, 2005
  2. Robert Hass

    1000BaseTX comparing to 1000BaseSX

    Robert Hass, Oct 6, 2005, in forum: Cisco
    Replies:
    1
    Views:
    990
    thrill5
    Oct 11, 2005
  3. Tom Dacon

    Comparing practice tests...

    Tom Dacon, Dec 4, 2003, in forum: MCSD
    Replies:
    2
    Views:
    472
    Curly
    Dec 5, 2003
  4. =?Utf-8?B?UGV0ZSBCLg==?=

    Comparing 70-320

    =?Utf-8?B?UGV0ZSBCLg==?=, Dec 7, 2004, in forum: MCSD
    Replies:
    3
    Views:
    519
    =?Utf-8?B?UGV0ZSBCLg==?=
    Dec 8, 2004
  5. afs_solutions@yahoo.ca

    Comparing registry files & detailing the differences

    afs_solutions@yahoo.ca, Nov 25, 2005, in forum: MCSE
    Replies:
    0
    Views:
    467
    afs_solutions@yahoo.ca
    Nov 25, 2005
Loading...

Share This Page