vba: How do I write a "For Each Statement" nested in a "With Statement"?
 

-My For Each Statement wont work:

Im trying to print a customer record in a report for each occurance of
a date that is seached for. If the end user is looking for the date
10/23/2004, they input it into an input box, its converted from a
string to a date (dtReportDate) and if the date is found, each record
containing that date will create a new record in my report.

Does anyone have any ideas on how how to fix this, am I placing it in
the right location of my code?

Thank you for your help.-



With Worksheets(1)
Set rng = .Columns(68).Find(dtReportDate)
Debug.Print "Displaying Quarried Results to Report"

If Not rng Is Nothing Then
*For Each dtReportDate In rng*

'Comments on Next Action (Cust.Contact.Log)
'Enrolled Date
rngactive.Offset(4, 0).Value = .Cells(rng.Row,
68).Value
'Next Action comments
rngactive.Offset(4, 1).Value = .Cells(rng.Row,
69).Value
'SSN
rngactive.Offset(4, 2).Value = .Cells(rng.Row,
6).Value
'Acct
rngactive.Offset(4, 3).Value = .Cells(rng.Row,
14).Value
'L Name
rngactive.Offset(4, 4).Value = .Cells(rng.Row,
3).Value
'F Name
rngactive.Offset(4, 5).Value = .Cells(rng.Row,
4).Value
'Enrolled
rngactive.Offset(4, 6).Value = .Cells(rng.Row,
1).Value
*Next dtReportDate*


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


--
Mcasteel
------------------------------------------------------------------------
Mcasteel's Profile: http://www.excelforum.com/member.php...o&userid=15698
View this thread: http://www.excelforum.com/showthread...hreadid=276390

How do I write a "For Each Statement" nested in a "With Statement"?
 

Hi
how is 'dtReportDate' defined?

--
Regards
Frank Kabel
Frankfurt, Germany

"Mcasteel" schrieb im
Newsbeitrag ...

-My For Each Statement wont work:

Im trying to print a customer record in a report for each occurance
of
a date that is seached for. If the end user is looking for the date
10/23/2004, they input it into an input box, its converted from a
string to a date (dtReportDate) and if the date is found, each record
containing that date will create a new record in my report.

Does anyone have any ideas on how how to fix this, am I placing it in
the right location of my code?

Thank you for your help.-



With Worksheets(1)
Set rng = .Columns(68).Find(dtReportDate)
Debug.Print "Displaying Quarried Results to Report"

If Not rng Is Nothing Then
*For Each dtReportDate In rng*

'Comments on Next Action (Cust.Contact.Log)
'Enrolled Date
rngactive.Offset(4, 0).Value = .Cells(rng.Row,
68).Value
'Next Action comments
rngactive.Offset(4, 1).Value = .Cells(rng.Row,
69).Value
'SSN
rngactive.Offset(4, 2).Value = .Cells(rng.Row,
6).Value
'Acct
rngactive.Offset(4, 3).Value = .Cells(rng.Row,
14).Value
'L Name
rngactive.Offset(4, 4).Value = .Cells(rng.Row,
3).Value
'F Name
rngactive.Offset(4, 5).Value = .Cells(rng.Row,
4).Value
'Enrolled
rngactive.Offset(4, 6).Value = .Cells(rng.Row,
1).Value
*Next dtReportDate*


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


--
Mcasteel
---------------------------------------------------------------------
---
Mcasteel's Profile:
http://www.excelforum.com/member.php...o&userid=15698
View this thread:
http://www.excelforum.com/showthread...hreadid=276390