IcePirate
09-05-2008, 07:27 AM
Hey,
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 in 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 sheet1 and the 2,000 numbers and compares them to sheet2 to the 30,000 lines and in sheet 3, column A I want to display the numbers that are there, and not display the the numbers that aren't there...
Or - I would like it to display all 2,000 numbers in sheet3, and in column B, put a yes, or no if the number was within the 30,000 lines or not...So I developed a VB code to do this...its below - but its not liking this line:
Application.ScreenUpdating = False
Here is the full script any suggestions?
Sub DupNumbers()
Dim LR1, LR2, LR3 As Integer
Dim x As Range
Dim yesno As String
Application.ScreenUpdating = False
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
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 in 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 sheet1 and the 2,000 numbers and compares them to sheet2 to the 30,000 lines and in sheet 3, column A I want to display the numbers that are there, and not display the the numbers that aren't there...
Or - I would like it to display all 2,000 numbers in sheet3, and in column B, put a yes, or no if the number was within the 30,000 lines or not...So I developed a VB code to do this...its below - but its not liking this line:
Application.ScreenUpdating = False
Here is the full script any suggestions?
Sub DupNumbers()
Dim LR1, LR2, LR3 As Integer
Dim x As Range
Dim yesno As String
Application.ScreenUpdating = False
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