Need help-For loop

S

SR

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

John Bundy

Try ending your with before this:
If Rng Is Nothing Then

and delete it from below

and you are having it call the next sheet only if the Rng is nothing, i'm
not sure that is what you are wanting.
 
S

SR

if the dte is not found on the current sheet ,we need to check if dte is
present on the next sheet. We need to make sure that we are checking for dte
in all the sheets in the workbook. And the important thing is that we should
check for dte only on column A of all sheets.
 
O

OssieMac

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

VBA Coding Help for Beginner 0
Textbox input goes to 2 sheets in same wbk 0
Excel hangs 1
Duplicate declaration in scope 2
Copy filtered data to sheet 2 1
Select Case Code does not run... 10
Error 2042 problem with vlookup 0
Please help with Custom Views 0

Top