Comparing two List

[dbagge]

I am using an Excel workbook which uses two sheets:
Bkorder - that holds a list of parts that are on backorder.
Orders - that holds a list of parts that have been ordered.

I have to compare the Part # (Column F) on the Orders sheet to the Supplier
Part # (Column A) of the Bkorder sheet. If there is a match then I pull the
number parts on the past due list (Bkorder - column H) to the Orders sheet
(to column L - back order) so parts can be ordered. The Bkorder sheet is
updated daily and so a new list is created daily.

I have been using the vba function below with no problems until the company
started using part #'s that no longer contained text (example: 2314R123-76
and now we also have 8246537):

Sub SortBkorder()
'
' SortBkorder Macro
' Macro recorded 12/21/2007
'

Dim cnt As Integer, cnum As Integer
cnt = 1
cnum = 6
'Count the number of rows used on Bkorder sheet
Sheets("Bkorder").Select
Range("B2").Select
Do Until ActiveCell.Value = ""
cnt = cnt + 1
ActiveCell.Offset(1, 0).Select
Loop
'Sort Bkorder sheet by Supplier Part
Sheets("Bkorder").Select
Range("A1:M" & cnt).Sort Key1:=Range("A2"), Order1:=xlAscending,
Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

'Set focus at A2
Range("A2").Select

'Go back to Orders sheet
Sheets("Orders").Select

'Go to Column L and put in function to get data
Range("L6").Select
Do Until ActiveCell.Offset(0, -6).Value = ""
ActiveCell.Value = "=IF(ISERROR(VLOOKUP(TRIM($F" & cnum &
"),Bkorder!$A$1:$M$" & cnt & ",8,False)),0,(VLOOKUP(TRIM($F" & cnum &
"),Bkorder!$A$1:$M$" & cnt & ",8,False)))"
cnum = cnum + 1
ActiveCell.Offset(1, 0).Select
Loop

'Sort Orders sheet by Maycor Backorder Qty
Range("A5:N" & cnum).Sort Key1:=Range("L6"), Order1:=xlDescending,
Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

'Set focus on column L and Row 6
Range("L6").Select

End Sub


All the other part numbers work correctly except the new numbers that show 0
on the Orders sheet but show 10 parts on back order on the Bkorder sheet.

Hope this isn't too confusing.

 

[john]

Not even sure if I have correctly understood what you are doing but you
probably could try returning values in VBA rather than using formula.

I cobbled this together - it may or may not be what you are looking for -
adjust as required.

Sub SortBkorder()
Dim BKOws As Worksheet
Dim ORDws As Worksheet
Dim foundcell As Range
Dim search As String

Set BKOws = Worksheets("Bkorder")
Set ORDws = Worksheets("Orders")


'Sort Bkorder sheet by Supplier Part
With BKOws
lastRow = .Cells(.Rows.Count, 2).End(xlUp).Row

.Range("A1:M" & lastRow).Sort Key1:=.Range("A2"),
Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1,
MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End With

'Go back to Orders sheet
With ORDws

.Activate

cnum = 6

'get data
Do
search = .Range("F" & cnum).Value

Set foundcell = BKOws.Columns(1).Find(search, LookIn:=xlValues,
LookAt:=xlWhole)

If foundcell Is Nothing = False Then
.Range("L" & cnum).Value = foundcell.Offset(0, 7).Value
Else
.Range("L" & cnum).Value = 0
End If

cnum = cnum + 1

Loop Until .Range("F" & cnum).Value = ""

'Sort Orders sheet by Maycor Backorder Qty
.Range("A5:N" & cnum).Sort Key1:=.Range("L6"), Order1:=xlDescending,
Header:= _
xlGuess, OrderCustom:=1,
MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

'Set focus on column L and Row 6
.Range("L6").Select

End With
End Sub

 

[dbagge]

John,

I still have a problem. I like the idea of using VBA to return the values
though. Because the part numbers on the Orders sheet may contain spaces it
(at the end of the part number) it won't show a match. Is there a way I can
trim those part numbers first (column F).

Thanks for your help,