Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Loop Statement through If Not IsEmpty Then Paste into Destination | Excel Discussion (Misc queries) | |||
Help with IsEmpty | Excel Programming | |||
isempty | Excel Programming | |||
vba: isempty | Excel Programming | |||
IsEmpty and Is Nothinq problems | Excel Programming |