Just put an Exit Do after the processing when a match is found (for both
sheets)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"helmekki " wrote in message
...
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!!!!!!!

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

yours
hesham

Attachment filename: vba to reduce stock by sales (1)+ auto invoice.xls
Download attachment:
http://www.excelforum.com/attachment.php?postid=568645
---
Message posted from http://www.ExcelForum.com/