I have a workbook that contains 5 worksheets, i want to loop throug
only three sheets that contains my data until the match is found(se
the macro), then stop the looping and proceed the rest of th
macro.......

here is the full macro with my small try to loop through two sheets(
and 2)
i tried it onc using Do....loop and another time using onl
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 & " wer
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
hesha

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