Retrieve Data Bug

Hi, so the guy before me at this firm used to run this VBA to retrieve data from other files. What it does is take a number from a separate file every day and then list it for the month.

I personally have limited knowledge of VBA. Today when I ran it (making no changes to the code) I got a 1004 error where the path name and date pulled was wrong. In debug, I highlighted the problem code in red. Any help would be much appreciated, thank you!

Sub GainLoss()

Dim path As String
Dim GCellx As Integer, GCellY As Integer, r As Double, i As Integer

Dim Year_d1 As Integer, Month_d1 As Variant, Day_d1 As Variant

i = 1
While Range("Date").Offset(i, 0) <> 0

    Year_d1 = Right(Range("Date").Offset(i, 0), 4)
    Month_d1 = Mid(Range("Date").Offset(i, 0), 4, 2)
        If Len(Month_d1) = 1 Then
            Month_d1 = "0" & Month_d1
        End If
    Day_d1 = Left(Range("Date").Offset(i, 0), 2)
        If Len(Day_d1) = 1 Then
            Day_d1 = "0" & Day_d1
        End If

If Range("Fund").Offset(0, 1) = "HTAAA" Then
    Range("Path").Offset(i, 0) = "H:\public\pyeung\Valuation\HTAAA\xxx_" & Year_d1 & Month_d1 & Day_d1 & ".xls"
ElseIf Range("Fund").Offset(0, 1) = "HTACF" Then
    Range("Path").Offset(i, 0) = "H:\public\pyeung\Valuation\HTACF\xxx_" & Year_d1 & Month_d1 & Day_d1 & ".xls"
End If

Application.ScreenUpdating = False
Workbooks.Open filename:=Range("Path").Offset(i, 0)

GCellx = ActiveSheet.Cells.Find("Net Return").Row + 1
GCellY = ActiveSheet.Cells.Find("Net Return").Column
r = Cells(GCellx, GCellY)

ActiveWorkbook.Close savechanges:=False
Application.ScreenUpdating = True

Range("Return").Offset(i, 0) = r

i = i + 1
Wend

End Sub

Hi and welcome to the forum

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE] [/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

I will add them for you - this time