Loop not working...

[yunesm]

Hello,
I have a macro that I just can't get to work. Everything up to the loop works as expected. I'm not sure why this isn't working!

The macro should take the data from the data entry tab and bring the data to the individual data worksheets (one per tab). I have this working on a different survey project but can't seem to get this one to work.

Any thoughts or advice would be greatly appreciated. The test file is attached.

Option Explicit

Sub Log_PtSat_Data()
  Dim I As Integer
  Dim Facility As String ' User confirmation of pracice location.
  Dim DateRng As Range 'User defined Month of data collection.  Will be used to define column position in loop.
  Dim LastEntry As Range 'Last used row within column.  Will be used to define the raw range of data to transfer as well as transfer date
  Dim RawRng As Range 'Rnage of data to transfer. Will be used in loop to transfer data to individual tables.
  Dim shtArray As Variant 'Data table worksheets. Will be used in loop to transfer data to individual tables.
  Dim SrcWks As Worksheet 'Data entry tab as variable
  Dim DstWks As Worksheet 'Individual worksheets from Array. Will be used in loop to transfer data to individual tables.
  Dim EntryDate 'User defined Month of data collection. Calculated by last column position.  Will be used to define column position in loop.
  Dim FacRng 'User defined practice location where patient data was collected.  Will be used to define row position in loop.

    shtArray = Array("ARRVD_TBL", _
    "RCVD_TBL", _
    "SRVD_TBL", _
    "EXCEL_TBL", _
    "VGOOD_TBL", _
    "GOOD_TBL", _
    "FAIR_TBL", _
    "POOR_TBL", _
    "PExcel_TBL")

    Set SrcWks = Worksheets("D A T A     E N T R Y")
    Facility = SrcWks.Range("B4")

    Set LastEntry = SrcWks.Cells.Find("*", , xlValues, xlWhole, xlByRows, xlPrevious, False)
      If Not LastEntry Is Nothing Then
         If LastEntry.Address = "$A$14" Then Exit Sub
            Set RawRng = LastEntry.Offset(-8, 0).Resize(9, 1)
            EntryDate = LastEntry.Offset(-9, 0).Text
      End If
      
      For I = 0 To UBound(shtArray)
        Set DstWks = ThisWorkbook.Worksheets(shtArray(I))
           Set DateRng = DstWks.Rows(1).Find(EntryDate, , xlValues, xlWhole, xlByColumns, xlPrevious, False) 'search first row on each sheet within sheet array for entry date for column position
           Set FacRng = DstWks.Columns(1).Find(Facility, , xlValues, xlWhole, xlByRows, False) 'search first column on each sheet within sheet array for facility for row position
           If Not DateRng Is Nothing And Not FacRng Is Nothing Then 'if neither date range or facility isn't found
              DstWks.Cells(FacRng.Row, DateRng.Column) = RawRng.Cells(I + 1, 1) 'Use row and colum position of entry date and facility matches to bring in data from data entry range
           End If
      Next I

End Sub

[Kyle123]

The loop is working fine, your problem is the formatting of your date,

this line:

EntryDate = LastEntry.Offset(-9, 0).Text

Should be:

EntryDate = Format(LastEntry.Offset(-9, 0).Value, "mm/dd/yy")

Your code is failing because you are trying to find "Oct-11" rather than "10/01/11"

[yunesm]

I figured I was missing something obvious!

Thank you!