need help to boost speed of macro

[ANKUSHRS1]

I have written a macro in order to check booking and remaining stock status. i have over 4000 cases which i want to check another data containing +4000 cases as well.
the macro is too slow who take over 20 sec for every case. kindly gimme a idea so that i can boost this macro to save time.

Sub remaining_stock()
Dim i As Long, c As Long, i1 As Long, c1 As Long

Dim qly As String
Dim qly1 As String

Dim shd As String
Dim shd1 As String


Dim qty As Double
Dim qty1 As Double
Dim qtyR As Double

  


c = Sheets("WH STOCK").Cells(Sheets("WH STOCK").Rows.Count, "a").End(xlUp).Row

c1 = Sheets("Bookings").Cells(Sheets("Bookings").Rows.Count, "a").End(xlUp).Row

For i = 2 To c
For i1 = 2 To c1

Application.StatusBar = i & " Cases out of " & c & " Bookings " & i1 & " Cases out of " & c1
qly = Worksheets("WH STOCK").Range("a" & i).Value
qly1 = Worksheets("Bookings").Range("a" & i1).Value

shd = Worksheets("WH STOCK").Range("b" & i).Value
shd1 = Worksheets("Bookings").Range("b" & i1).Value

qty = Worksheets("WH STOCK").Range("c" & i).Value
qty1 = Worksheets("Bookings").Range("c" & i1).Value
qtyR = Worksheets("WH STOCK").Range("D" & i1).Value




If qly = qly1 And shd = shd1 Then

Worksheets("WH STOCK").Range("d" & i).Value = qty1

End If


Worksheets("WH STOCK").Range("E" & i).Value = qty - Worksheets("WH STOCK").Range("D" & i).Value

If Worksheets("WH STOCK").Range("e" & i).Value < 0 Then

Worksheets("WH STOCK").Range("f" & i).Value = "Overbookings"

End If

If Worksheets("WH STOCK").Range("e" & i).Value = 0 Then

Worksheets("WH STOCK").Range("f" & i).Value = "Stock Finished"

End If



Next



Next



End Sub

 

[Hui]

Are you able to post a sample file with say 20 rows of data pls?

 

[ANKUSHRS1]

pls see attachment

 

[ANKUSHRS1]

I just find a flow .....i can use [exit for] when condition match...but it is not much effective this data because matching cases very few....pls help

 

[Hui]

How about this solution which is nearly instantaneous and doesn't need VBA?

On the Bookings worksheet select the Data including headings,
Goto the Formula, Create From Selection Tab
Select Top Row, Apply

On WH Stock worksheet

D2: =SUMPRODUCT((MATERIAL_CODE=A2)*(SHADE=B2)*(Total))
E2: =IF(D2>0,C2-D2,C2)
F2: =IF(E2<0,"Overbookings",IF(E2=0,"Stock finished",""))
Copy all down

see attached file

 

[p45cal]

Taking Hui's idea to VBA:

Sub Macro2c()
Set BookingsRng = Sheets("Bookings").Range("A1").CurrentRegion.Resize(, 3)
MCAddr = BookingsRng.Columns(1).Address(ReferenceStyle:=xlR1C1, external:=True)
ShdAddr = BookingsRng.Columns(2).Address(ReferenceStyle:=xlR1C1, external:=True)
TotAddr = BookingsRng.Columns(3).Address(ReferenceStyle:=xlR1C1, external:=True)
Set myrng = Sheets("WH STOCK").Range("A1").CurrentRegion.Columns(1)
Set myrng = myrng.Offset(1).Resize(myrng.Cells.Count - 1)
myrng.Offset(, 3).FormulaR1C1 = "=SUMIFS(" & TotAddr & "," & MCAddr & ",RC[-3]," & ShdAddr & ",RC[-2])"
myrng.Offset(, 4).FormulaR1C1 = "=IF(RC[-1]>0,RC[-2]-RC[-1],RC[-2])"
myrng.Offset(, 5).FormulaR1C1 = "=IF(RC[-1]<0,""Overbookings"",IF(RC[-1]=0,""Stock finished"",""""))"
myrng.Offset(, 3).Resize(, 3).Value = myrng.Offset(, 3).Resize(, 3).Value
End Sub

Hui, I used SUMIFS instead of SUMPRODUCT since it's about 5 times a s fast.