Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > General Computer Discussion > General Computer Support > Comparing Data

Thread Tools

Comparing Data

IcePirate IcePirate is offline
Junior Member
Join Date: Sep 2008
Posts: 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)
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
For Each x In Range("A" & LR1)
    If IsError(Application.WorksheetFunction.Match(x, Sheets(2).Range("A1:A" & LR2), 0)) Then
        yesno = "No"
        yesno = "Yes"
    End If
    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
Reply With Quote

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
Comparing string input to enum data type C Programming 35 03-01-2007 02:51 PM
Comparing data in a gridview Nick Hodge \(MVP\) ASP .Net 2 07-24-2006 09:49 PM
comparing field data from previous field in loop with null values ASP General 6 02-15-2006 01:59 PM
XSLT: Selecting nodes by comparing data AFTER conversion Ryan Adler XML 2 01-31-2006 09:53 AM
comparing multiple copies of terrabytes of data? Dan Stromberg Python 11 10-29-2004 04:46 AM