Here is the complete code for the sub. 1st I load my variables a = my item number, b = the qty and c = the week start date. ( I have the variable set to nothing to begin with becuase I will loop through this entire process and when I start the loop again I want my varibles emptied out). I then move to the 1st tab and find the date that matches variable c in row 20. At that point my code looks for the cell in column a that has the item number that matches variable a. When it finds that item I load variable b into the empty cell were they intersect. My if statement is basically moving my activecell down the column my date was found in until it finds that correct item in column a. Thats a total of 9,194 cells it may have to check which is why it takes so long to run.
Sub Load()
Dim a As String
Dim b As Long
Dim c As Date
Dim d
Dim e
Sheets("BPCS Forecast File").Select
Range("A2").Select
Do Until ActiveCell.Value = ""
'Load the item as variable a, qty as variable b, and date of week as variable c
a = ActiveCell.Value
b = ActiveCell.Offset(0, 1).Value
c = ActiveCell.Offset(0, 3).Value
ActiveCell.Offset(1, 0).Select
' check for more than one line with the same item and date. add to varible b if found
Do While ActiveCell.Value = ActiveCell.Offset(-1, 0).Value And ActiveCell.Offset(0, 3).Value = ActiveCell.Offset(-1, 3).Value
b = b + ActiveCell.Offset(0, 1).Value
ActiveCell.Offset(1, 0).Select
Loop
'loacte the correct date
Sheets("Forecast Detail").Select
Range("E20").Select
Do Until ActiveCell.Value = c Or ActiveCell.Value = ""
If ActiveCell.Value <> c Then
ActiveCell.Offset(0, 1).Select
End If
Loop
'find the item number
Do Until Cells(Application.ActiveCell.Row, 1).Value = a Or Cells(Application.ActiveCell.Row, 1).Value = ""
If Cells(Application.ActiveCell.Row, 1).Value <> a Then
ActiveCell.Offset(1, 0).Select
End If
Loop
ActiveCell.Value = b
Sheets("BPCS Forecast File").Select
Loop
End Sub
Bookmarks