Hi,

I'm writing a macro that will import data from one workbook to another, based on yesterdays date. I have the import functionality working, and I've been playing all afternoon, but I can't find a way to find yesterdays date in a range and use that row number as the row offset value in this line:
Imprt.Offset(14, 0).Offset(0, 1).Value = c.Offset(0, 5).Value
Rather than having a set row offset value (in this case, 14) I would like to use the find function to lookup yesterdays date in range "B50:B80" in the worksheet I've designated as "sh1" and return the row number of the cell that has yesterdays date and use this value as the row offset value, replacing the hard coded 14 that is in there currently. The date values in the range are formatted as per Date Format.jpg.

My code is below:

 Sub Import_Trucking()
    Dim wb As Workbook, wb2 As Workbook
    Dim sh1 As Worksheet, sh2 As Worksheet
    Dim strPath As String, strFolder As String, strYear As String, strFname As String, strSheetName As Double
    Dim lr As Long, c As Range, Imprt As Range, rng As Range

     
    Set wb = ThisWorkbook
    Set sh1 = ActiveSheet
        

    strPath = "G:\Mining_Geology\06. Production\01. Daily Production\03. Truck Haulage\"
    strYear = Format(Date - 1, "yyyy") & "\"
    strFolder = strPath & strYear
    strFname = "*" & Format(Date - 1, "mmm") & ".xlsx"
    strSheetName = Format(Date - 1, "d")
     
    ChDrive "g"
    ChDir strFolder
     
    Workbooks.Open Filename:=(strFolder & strFname)
    Sheets(strSheetName).Select
    Set wb2 = ActiveWorkbook
    Set sh2 = Sheets(strSheetName)
    
   

    Set rng = sh2.Range("C38:C47")
        For Each c In rng
                Set Imprt = sh1.Range("C47:CL47").Find(c.Value, , xlValues)
                If Not Imprt Is Nothing Then
                    Imprt.Offset(14, 0).Offset(0, 1).Value = c.Offset(0, 5).Value
                    Imprt.Offset(14, 0).Offset(0, 3).Value = c.Offset(0, 6).Value
                End If
        Next
          
              
    Application.CutCopyMode = False
    Application.EnableEvents = True
     
End Sub
Many thanks,
Dan