I am trying to do a number of if then loops whilst inside a while wend loop. I am getting the error . Compile error WEND without While. I have these but obvisouly in my loops it is not rght. I have attached the sheet so you can see what it is doing.
Thanks for your help
hi, luffield, it looks you are missing End If statement:
Sub Summarised_LG3() ' Summarises LG3 by WIP Dim Quoted As Currency Dim In_Progress As Currency Dim Lab_Sold As Currency Dim Parts_Sold As Currency Dim Total_Sold As Currency Dim Lab_Deferred As Currency Dim Parts_Deferred As Currency Dim Total_Deferred As Currency Dim Lab_Deleted As Currency Dim Parts_Deleted As Currency Dim Total_Deleted As Currency Dim condition As String ' Sort by Condition then WIP Number Cells.Select ActiveWorkbook.Worksheets("LG3").Sort.SortFields.Clear Range("M1").Activate ActiveWorkbook.Worksheets("LG3").Sort.SortFields.Add Key:=Range("U2:U129"), _ SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal ActiveWorkbook.Worksheets("LG3").Sort.SortFields.Add Key:=Range("E2:E129"), _ SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal With ActiveWorkbook.Worksheets("LG3").Sort .SetRange Range("A1:AZ129") .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With ' Find last row x = 2 While Sheets("LG3").Cells(x, 1) <> "" y = y + 1 x = x + 1 Wend y = y + 2 ' set values x = 2 sumx = 2 Quoted = 0 In_Progress = 0 Lab_Sold = 0 Parts_Sold = 0 Lab_Deferred = 0 Parts_Deferred = 0 Lab_Deleted = 0 Parts_Deleted = 0 WIP = Sheets("LG3").Cells(x, 5) Operator = Sheets("LG3").Cells(x, 4) condition = Sheets("LG3").Cells(x, 21) '----------------------------------------------------------------------------------------------------------------------------- While x <= y If Sheets("LG3").Cells(x, 14) = condition Then If Sheets("LG3").Cells(x, 5) = WIP Then Quoted = Quoted + Sheets("LG3").Cells(x, 6) In_Progress = In_Progress + Sheets("LG3").Cells(x, 7) Lab_Sold = Lab_Sold + Sheets("LG3").Cells(x, 8) Parts_Sold = Parts_Sold + Sheets("LG3").Cells(x, 9) Total_Sold = Lab_Sold + Parts_Sold Lab_Deferred = Lab_Deferred + Sheets("LG3").Cells(x, 10) Parts_Deferred = Parts_Deferred + Sheets("LG3").Cells(x, 11) Total_Deferred = Lab_Deferred + Parts_Deferred Lab_Deleted = Lab_Deleted + Sheets("LG3").Cells(x, 12) Parts_Deleted = Parts_Deleted + Sheets("LG3").Cells(x, 13) Total_Deleted = Lab_Deleted + Parts_Deleted x = x + 1 Else Sheets("Summarised LG3").Cells(sumx, 1) = WIP Sheets("Summarised LG3").Cells(sumx, 2) = Operator Sheets("Summarised LG3").Cells(sumx, 3) = Quoted Sheets("Summarised LG3").Cells(sumx, 4) = In_Progress Sheets("Summarised LG3").Cells(sumx, 5) = Lab_Sold Sheets("Summarised LG3").Cells(sumx, 6) = Parts_Sold Sheets("Summarised LG3").Cells(sumx, 7) = Total_Sold Sheets("Summarised LG3").Cells(sumx, 8) = Lab_Deferred Sheets("Summarised LG3").Cells(sumx, 9) = Parts_Deferred Sheets("Summarised LG3").Cells(sumx, 10) = Total_Deferred Sheets("Summarised LG3").Cells(sumx, 11) = Lab_Deleted Sheets("Summarised LG3").Cells(sumx, 12) = Parts_Deleted Sheets("Summarised LG3").Cells(sumx, 13) = Total_Deleted Sheets("Summarised LG3").Cells(sumx, 14) = condition WIP = Sheets("LG3").Cells(x, 5) Operator = Sheets("LG3").Cells(x, 4) condition = Sheets("LG3").Cells(x, 21) Quoted = 0 In_Progress = 0 Lab_Sold = 0 Parts_Sold = 0 Total_Sold = 0 Lab_Deferred = 0 Parts_Deferred = 0 Total_Deferred = 0 Lab_Deleted = 0 Parts_Deleted = 0 Total_Deleted = 0 sumx = sumx + 1 End If Else condition = Sheets("LG3").Cells(x, 21) If Sheets("LG3").Cells(x, 5) = WIP Then Quoted = Quoted + Sheets("LG3").Cells(x, 6) In_Progress = In_Progress + Sheets("LG3").Cells(x, 7) Lab_Sold = Lab_Sold + Sheets("LG3").Cells(x, 8) Parts_Sold = Parts_Sold + Sheets("LG3").Cells(x, 9) Total_Sold = Lab_Sold + Parts_Sold Lab_Deferred = Lab_Deferred + Sheets("LG3").Cells(x, 10) Parts_Deferred = Parts_Deferred + Sheets("LG3").Cells(x, 11) Total_Deferred = Lab_Deferred + Parts_Deferred Lab_Deleted = Lab_Deleted + Sheets("LG3").Cells(x, 12) Parts_Deleted = Parts_Deleted + Sheets("LG3").Cells(x, 13) Total_Deleted = Lab_Deleted + Parts_Deleted x = x + 1 Else Sheets("Summarised LG3").Cells(sumx, 1) = WIP Sheets("Summarised LG3").Cells(sumx, 2) = Operator Sheets("Summarised LG3").Cells(sumx, 3) = Quoted Sheets("Summarised LG3").Cells(sumx, 4) = In_Progress Sheets("Summarised LG3").Cells(sumx, 5) = Lab_Sold Sheets("Summarised LG3").Cells(sumx, 6) = Parts_Sold Sheets("Summarised LG3").Cells(sumx, 7) = Total_Sold Sheets("Summarised LG3").Cells(sumx, 8) = Lab_Deferred Sheets("Summarised LG3").Cells(sumx, 9) = Parts_Deferred Sheets("Summarised LG3").Cells(sumx, 10) = Total_Deferred Sheets("Summarised LG3").Cells(sumx, 11) = Lab_Deleted Sheets("Summarised LG3").Cells(sumx, 12) = Parts_Deleted Sheets("Summarised LG3").Cells(sumx, 13) = Total_Deleted Sheets("Summarised LG3").Cells(sumx, 14) = condition WIP = Sheets("LG3").Cells(x, 5) Operator = Sheets("LG3").Cells(x, 4) condition = Sheets("LG3").Cells(x, 21) Quoted = 0 In_Progress = 0 Lab_Sold = 0 Parts_Sold = 0 Total_Sold = 0 Lab_Deferred = 0 Parts_Deferred = 0 Total_Deferred = 0 Lab_Deleted = 0 Parts_Deleted = 0 Total_Deleted = 0 sumx = sumx + 1 End If End If Wend End Sub
Thanks ever such a lot.
Watersev that works brill, however the last condition code is missing. Any idea's on what else I have got wrong ?
Thanks again
what do you mean by the last condition code is missing? I copied-pasted all the code in the file plus added End if, didn't I?
You did . No I think it is my code that is wrong.
Basically if you run the sheet and look at LG3 Summary the last line has no condition entered in the box on the summary despite it being there on the LG3 tab.
Sorry to be a pain...
try sort of this:
... your preceding code ... 'Find last row y = Cells(Rows.Count, 1).End(xlUp).Row + 1 Set mainsh = Sheets("LG3") ' set values x = 2 sumx = 2 WIP = mainsh.Cells(x, 5) Operator = mainsh.Cells(x, 4) condition = mainsh.Cells(x, 21) '----------------------------------------------------------------------------------------------------------------------------- While x <= y With mainsh If .Cells(x, 14) = condition Then If .Cells(x, 5) = WIP Then Quoted = Quoted + .Cells(x, 6) In_Progress = In_Progress + .Cells(x, 7) Lab_Sold = Lab_Sold + .Cells(x, 8) Parts_Sold = Parts_Sold + .Cells(x, 9) Total_Sold = Lab_Sold + Parts_Sold Lab_Deferred = Lab_Deferred + .Cells(x, 10) Parts_Deferred = Parts_Deferred + .Cells(x, 11) Total_Deferred = Lab_Deferred + Parts_Deferred Lab_Deleted = Lab_Deleted + .Cells(x, 12) Parts_Deleted = Parts_Deleted + .Cells(x, 13) Total_Deleted = Lab_Deleted + Parts_Deleted x = x + 1 Else With Sheets("Summarised LG3") .Cells(sumx, 1) = WIP .Cells(sumx, 2) = Operator .Cells(sumx, 3) = Quoted .Cells(sumx, 4) = In_Progress .Cells(sumx, 5) = Lab_Sold .Cells(sumx, 6) = Parts_Sold .Cells(sumx, 7) = Total_Sold .Cells(sumx, 8) = Lab_Deferred .Cells(sumx, 9) = Parts_Deferred .Cells(sumx, 10) = Total_Deferred .Cells(sumx, 11) = Lab_Deleted .Cells(sumx, 12) = Parts_Deleted .Cells(sumx, 13) = Total_Deleted .Cells(sumx, 14) = condition End With WIP = .Cells(x, 5) Operator = .Cells(x, 4) condition = .Cells(x, 21) Quoted = 0 In_Progress = 0 Lab_Sold = 0 Parts_Sold = 0 Total_Sold = 0 Lab_Deferred = 0 Parts_Deferred = 0 Total_Deferred = 0 Lab_Deleted = 0 Parts_Deleted = 0 Total_Deleted = 0 sumx = sumx + 1 End If Else If .Cells(x, 5) = WIP Then Quoted = Quoted + .Cells(x, 6) In_Progress = In_Progress + .Cells(x, 7) Lab_Sold = Lab_Sold + .Cells(x, 8) Parts_Sold = Parts_Sold + .Cells(x, 9) Total_Sold = Lab_Sold + Parts_Sold Lab_Deferred = Lab_Deferred + .Cells(x, 10) Parts_Deferred = Parts_Deferred + .Cells(x, 11) Total_Deferred = Lab_Deferred + Parts_Deferred Lab_Deleted = Lab_Deleted + .Cells(x, 12) Parts_Deleted = Parts_Deleted + .Cells(x, 13) Total_Deleted = Lab_Deleted + Parts_Deleted x = x + 1 Else With Sheets("Summarised LG3") .Cells(sumx, 1) = WIP .Cells(sumx, 2) = Operator .Cells(sumx, 3) = Quoted .Cells(sumx, 4) = In_Progress .Cells(sumx, 5) = Lab_Sold .Cells(sumx, 6) = Parts_Sold .Cells(sumx, 7) = Total_Sold .Cells(sumx, 8) = Lab_Deferred .Cells(sumx, 9) = Parts_Deferred .Cells(sumx, 10) = Total_Deferred .Cells(sumx, 11) = Lab_Deleted .Cells(sumx, 12) = Parts_Deleted .Cells(sumx, 13) = Total_Deleted .Cells(sumx, 14) = condition End With WIP = .Cells(x, 5) Operator = .Cells(x, 4) condition = .Cells(x, 21) Quoted = 0 In_Progress = 0 Lab_Sold = 0 Parts_Sold = 0 Total_Sold = 0 Lab_Deferred = 0 Parts_Deferred = 0 Total_Deferred = 0 Lab_Deleted = 0 Parts_Deleted = 0 Total_Deleted = 0 sumx = sumx + 1 End If End If End With Wend End Sub
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks