VBA: For Each... Statement, Expert?

[Mcasteel]

Hey guys:

Im having a problem with the placement of my for...each statement. Th
instructions are not being repeated as intended, I think my placemen
is wrong, do you have any suggestions? Do I need to use the statemen
outside of the With statement?

Thank you!




Sub GetDailyReport()

Dim strReportDate As String
strReportDate = InputBox("Please Enter Report Date:", "Get Dail
Report:")

Dim dtReportDate As Date

dtReportDate = DateValue(Date:=strReportDate)

ThisWorkbook.Sheets("Daily To Do").Select

'Set rngActive
Set rngactive = Application.ActiveCell

Debug.Print "Printing Report Date & Title"
rngactive.Offset(0, 0).Value = strReportDate
rngactive.Offset(0, 1).Value = "Daily 'To Do List'"

Debug.Print "Printing Report Header"
rngactive.Offset(1, 0).Value = "Follow Up Date"
rngactive.Offset(1, 1).Value = "Next Action"
rngactive.Offset(1, 2).Value = "SSN"
rngactive.Offset(1, 3).Value = "Account #"
rngactive.Offset(1, 4).Value = "Last"
rngactive.Offset(1, 5).Value = "First"
rngactive.Offset(1, 6).Value = "Enrolled"

'*****************************
Debug.Print "Searching for Matching Dates"
Debug.Print "user-input: " & strReportDate

Application.Workbooks(1).Activate

WITH WORKSHEETS(\"CUSTOMER TRACKING\") 'ALL DATA IS 'PULLED' FRO
SHEET: 'CUSTOMER TRACKING

Set rng = .Columns(68).Find(dtReportDate)



If Not rng Is Nothing Then



Dim cell As Range
FOR EACH CELL IN RNG
IF CELL.VALUE = DTREPORTDATE THE


'LOGIC: If the cells in column 'BP'(68) equal the date being seache
for then
'display customer record for that date. There may be serveral matchin
dates
'and therefore I need a loop statment to repeat instructions for eac
date
'in worksheet column 68 that matches the date the user is inputin
(dtReportDate)


Debug.Print "Printing " & dtReportDate & " matching results to report"
'Next Action Date
rngactive.Offset(3, 0).Value = .Cells(rng.Row, 68).Value
'Next Action Comments
rngactive.Offset(3, 1).Value = .Cells(rng.Row, 69).Value
'SSN
rngactive.Offset(3, 2).Value = .Cells(rng.Row, 6).Value
'Acct
rngactive.Offset(3, 3).Value = .Cells(rng.Row, 14).Value
'L Name
rngactive.Offset(3, 4).Value = .Cells(rng.Row, 3).Value
'F Name
rngactive.Offset(3, 5).Value = .Cells(rng.Row, 4).Value
'Date Enrolled
rngactive.Offset(3, 6).Value = .Cells(rng.Row, 1).Value
Debug.Print "All matching data sent to report for: " & dtReportDate


END IF
NEXT CEL




Else:
Debug.Print "No Matching Date (Next Action Step)"
MsgBox "No Action Needed for Date Searched (Next Action Step)."
End If




End With

rngactive.Offset(5, 0).Activate

End Su

 

[Guest]

I don't think the For Next loop is the culprit. I believe

Set rng = .Columns(68).Find(dtReportDate)

finds the FIRST cell where the condition is met and not ALL cells, therefore
the For Next loop acts on the FIRST cell only.