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