Do.....Loop until IsEmpty(....)
 

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

Do.....Loop until IsEmpty(....)
 

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/

Do.....Loop until IsEmpty(....)
 

helmekki,
The code below will search through the Range("A2:A10"), of the first three
sheets of the workbook for "strOrd" and do the rngStock.Value =
rngStock.Value - OrdVal of that sheet.
Cecil

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 Not Application.Intersect(Target, Range("B2:B10")) _
Is Nothing Then
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

For i = 1 To 3

If Not IsError _
(Application.Match(strOrd, Sheets(i).Range("A2:A10"), 0)) Then
lrow = Application.Match(strOrd, Sheets(i).Range("A2:A10"), 0)
Set rngStock = Sheets(i).Range("A1").Offset(lrow, 1)
rngStock.Value = rngStock.Value - OrdVal
End If

Next i

End If
Set rngStock = Nothing
err: Exit Sub
End Sub


"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/