Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help-For loop
Hi All,
I am new to excel programming, below is my code , it will take the date from user and search for the date in all the sheets in the workbook. I am getting an error with the for loop used. Can any one please help. dte = InputBox(" Please Enter Date in Format MM/DD/YYYY") If Not IsDate(dte) Then MsgBox " You have not Entered correct input " Worksheets("Sheet4").Range("test").ClearContents Worksheets("Sheet4").Range("test1").ClearContents Worksheets("Sheet4").Range("A6").Value = "Sorry no data found" End Else For Each wsSheet In Worksheets wsSheet.Activate With ActiveSheet.Range("A:A") Set Rng = .Find(What:=dte, _ After:=.Cells(.Cells.Count), _ LookIn:=xlValues, _ LookAt:=xlWhole, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) If Rng Is Nothing Then Next wsSheet Else Application.Goto Rng, True ActiveCell.Offset(1, 0).Select ActiveCell.Offset(0, 10).Select Do While IsEmpty(ActiveCell) = False If ActiveCell.Value = "XC" Then h = h + 1 ActiveCell.Offset(0, 6).Select If ActiveCell.Value = dte Then ht = ht + 1 End If ActiveCell.Offset(0, -6).Select End If If ActiveCell.Value = "XF" Then p = p + 1 ActiveCell.Offset(0, 6).Select If ActiveCell.Value = dte Then pt = pt + 1 End If ActiveCell.Offset(0, -6).Select End If If ActiveCell.Value = "XD" Then d = d + 1 ActiveCell.Offset(0, 6).Select If ActiveCell.Value = dte Then dt = dt + 1 End If ActiveCell.Offset(0, -6).Select End If If ActiveCell.Value = "DF" Then s = s + 1 ActiveCell.Offset(0, 6).Select If ActiveCell.Value = dte Then st = st + 1 End If ActiveCell.Offset(0, -6).Select End If ActiveCell.Offset(1, 0).Select Loop End If End With Worksheets("Sheet4").Activate Worksheets("Sheet4").Range("A6").Value = "Daily Report for " & dte Worksheets("Sheet4").Range("E4").Value = h Worksheets("Sheet4").Range("E5").Value = p Worksheets("Sheet4").Range("E6").Value = s Worksheets("Sheet4").Range("E7").Value = d Worksheets("Sheet4").Range("F4").Value = ht Worksheets("Sheet4").Range("F5").Value = pt Worksheets("Sheet4").Range("F6").Value = st Worksheets("Sheet4").Range("F7").Value = dt Worksheets("Sheet4").Range("F9").Value = h + p + s + d Worksheets("Sheet4").Range("F10").Value = ht + pt + st + dt End If Thanks in advance, |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help-For loop
Your conditional next wsSheet is wrong. You can't set it to conditionally
loop as you have done. Better to use If Not rng Is Nothing Then and then the next wsSheet goes at the end of the if/end if Try the following. I have not checked/tested any of the remaining code. dte = InputBox(" Please Enter Date in Format MM/DD/YYYY") If Not IsDate(dte) Then MsgBox " You have not Entered correct input " Worksheets("Sheet4").Range("test").ClearContents Worksheets("Sheet4").Range("test1").ClearContents Worksheets("Sheet4").Range("A6").Value = "Sorry no data found" End Else For Each wsSheet In Worksheets wsSheet.Activate With ActiveSheet.Range("A:A") Set rng = .Find(What:=dte, _ After:=.Cells(.Cells.Count), _ LookIn:=xlValues, _ LookAt:=xlWhole, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) If Not rng Is Nothing Then Application.Goto rng, True ActiveCell.Offset(1, 0).Select ActiveCell.Offset(0, 10).Select Do While IsEmpty(ActiveCell) = False If ActiveCell.Value = "XC" Then h = h + 1 ActiveCell.Offset(0, 6).Select If ActiveCell.Value = dte Then ht = ht + 1 End If ActiveCell.Offset(0, -6).Select End If If ActiveCell.Value = "XF" Then p = p + 1 ActiveCell.Offset(0, 6).Select If ActiveCell.Value = dte Then pt = pt + 1 End If ActiveCell.Offset(0, -6).Select End If If ActiveCell.Value = "XD" Then d = d + 1 ActiveCell.Offset(0, 6).Select If ActiveCell.Value = dte Then dt = dt + 1 End If ActiveCell.Offset(0, -6).Select End If If ActiveCell.Value = "DF" Then s = s + 1 ActiveCell.Offset(0, 6).Select If ActiveCell.Value = dte Then st = st + 1 End If ActiveCell.Offset(0, -6).Select End If ActiveCell.Offset(1, 0).Select Loop End If End With Next wsSheet Worksheets("Sheet4").Activate Worksheets("Sheet4").Range("A6").Value = "Daily Report for " & dte Worksheets("Sheet4").Range("E4").Value = h Worksheets("Sheet4").Range("E5").Value = p Worksheets("Sheet4").Range("E6").Value = s Worksheets("Sheet4").Range("E7").Value = d Worksheets("Sheet4").Range("F4").Value = ht Worksheets("Sheet4").Range("F5").Value = pt Worksheets("Sheet4").Range("F6").Value = st Worksheets("Sheet4").Range("F7").Value = dt Worksheets("Sheet4").Range("F9").Value = h + p + s + d Worksheets("Sheet4").Range("F10").Value = ht + pt + st + dt End If End Sub -- Regards, OssieMac |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
returning back to loop check condition without completing the loop | Excel Programming | |||
Loop to Filter, Name Sheets. If Blank, Exit Loop | Excel Programming | |||
Naming Worksheets - Loop within a loop issue | Excel Programming | |||
Naming Worksheets - Loop within a loop issue | Excel Programming | |||
Advancing outer Loop Based on criteria of inner loop | Excel Programming |