I have a workbook that contains 5 worksheets, i want to loop through only three sheets that contains my data until the match is found(see the macro), then stop the looping and proceed the rest of the macro.......
here is the full macro with my small try to loop through two sheets(4 and 2)
i tried it onc using Do....loop and another time using only if........but!!!!!!!
I attached the file
would any one pls help.............
I placed this macro in the stock sheet
Private Sub Worksheet_Change(ByVal Target As Range) Dim OrdVal As Long Dim strOrd As String Dim lrow As Long Dim rngStock As Range Dim myCheck As Integer On Error GoTo err If Application.Intersect(Target, Range("B2:B10")) Is Nothing Then 'Exit Sub Else OrdVal = Target.Value strOrd = Target.Offset(0, -1).Value myCheck = MsgBox("" & OrdVal & " Units of " & strOrd & " were ordered", vbYesNo) If myCheck = vbNo Then Exit Sub End If Do Until IsEmpty(strOrd) If lrow = Application.Match(strOrd, Sheet4.Range("A2:A10"), 0) Then Set rngStock = Sheets("Stock").Range("A1").Offset(lrow, 1) rngStock.Value = rngStock.Value - OrdVal Else lrow = Application.Match(strOrd, Sheet2.Range("A2:A10"), 0) Set rngStock = Sheets("Stock").Range("A1").Offset(lrow, 1) rngStock.Value = rngStock.Value - OrdVal End If Loop End If Set rngStock = Nothing err: Exit Sub End Sub