Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
SR SR is offline
external usenet poster
 
Posts: 18
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 2,510
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
returning back to loop check condition without completing the loop ashish128 Excel Programming 13 April 3rd 08 12:53 PM
Loop to Filter, Name Sheets. If Blank, Exit Loop ryguy7272 Excel Programming 3 February 5th 08 04:41 PM
Naming Worksheets - Loop within a loop issue klysell Excel Programming 5 March 29th 07 05:48 AM
Naming Worksheets - Loop within a loop issue klysell Excel Programming 0 March 27th 07 11:17 PM
Advancing outer Loop Based on criteria of inner loop ExcelMonkey Excel Programming 1 August 15th 05 05:23 PM


All times are GMT +1. The time now is 04:14 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"