VBA: For Each.... wont work correctly / is placement wrong?

M

Mcasteel

Hey guys:

Im having a problem with the placement of my for...each statement. Th
instructions are not being repeated as intded, I think my placement i
wrong, do you have any suggestions?

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_seached_for_then
________________'display_customer_record_for_that_date.__There_may_be_serveral_matching_dates
________________'and_therefore_I_need_a_loop_statment_to_repeat_instructions_for_each_date
________________'in_worksheet_column_68_that_matches_the_date_the_user_is_inputing_(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 repor
for: " & dtReportDate


END IF
NEXT CEL




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




End With

rngactive.Offset(5, 0).Activate

End Su
 

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

Top