Apply start of month to previous tax year: Subscript out of range

I am trying to write a macro that loops through a sort table, creates a copy of template for each financial year it finds then copies and pastes the data from the sort table into the appropriate cells on the corresponding worksheet.

I have code that loops through the data and creates a copy of the worksheet for each financial year.

I have written the bit that loops through the sort table and returns the variables I need in order to paste payment data to the correct Worksheet:
-Tax year Year (Which is what I have named the worksheets),
-Financial Month which dictates the destination column and
-Charge Type which decides the destination column and Cash which is the value I want to copy and paste.

The functions FY (returns the financial year) and FM (returns the Financial month) from a string with the date in format DD.MM.YYYY.
I use these functions to create variables that provides the co-ordinates for where I want call values pasted.

Worksheets(TY).Cells(16, Cashcol).End(xlUp).Offset(1, 0).Value = Cash

TY= Year (What I have used to name the worksheets.
16 is the last row in the range I want to copy to.
Cashcol is the column I want.
xlUp is a constant edited because I didn't know this when I first asked the question.

This works for all dates between 5th of May to 31st of April. (Month’s 1 to most of 12.)

Due to the Tax year running 6th April to 5th April there is a period from 1st of May to 4th of May l that belongs in the previous Tax Year.

This means that FM returns a month of 0.

To fix this I added the lines:

If Cashmonth = 0 Then Backyear = True
      
If Backyear = True Then TY = FY(EDP) - 1
If Backyear = True Then Cashcol = 51

EG. 03.05.2023 is in Month 12 of 2022 Tax Year.
Which I want to go to first empty cell above row 16 in column 51 of the worksheet named 2022.

I get an error that doesn’t happen when Backyear=false.

Subscript out of range

Debugging shows that all other variables are correct (TY =2022, Cashcol = 51, Cash= the correct amount). xlUp returns -4162 which I now know is correct.

How have I managed to break what, outside of 5 days in May, is a working code?

Slightly redacted version of the full code:

Option Explicit


Public Function FY(Fdate As String) As String
Dim arDMY As Variant

arDMY = Split(Fdate, ".")
If arDMY(1) >= 5 Then
   FY = CStr(arDMY(2) + 1)
Else
  FY = CStr(arDMY(2))
End If
End Function


Public Function FM(Fdate As String) As String
Dim arDMY As Variant
Dim TM As String
arDMY = Split(Fdate, ".")
If arDMY(1) >= 5 Then
   TM = CStr(arDMY(1) - 4)
Else
  TM = CStr(arDMY(1) + 8)
End If

If arDMY(0) >= 5 Then
   FM = TM
Else
  FM = TM - 1
End If
End Function


Sub Move_stuff()
Dim cell As Range
Dim Todate As Range
Dim Charge As Range
Dim Duedate As Range

Dim Sort_Table As Worksheet
Dim Template As Worksheet

Dim C As Variant
Dim D As Variant
Dim Targ As Range
Dim Ddate As Range
Dim Charge_Amount As Range
Dim Targdate As String
Dim EDP As String
Dim Cash As Double
Dim Backyear As Boolean
Dim Pos As Variant
Dim Cashmonth As String
Dim TY As Variant
Dim EDPM As Variant
Dim Cashcol As Long

Dim Year As Variant
Dim Month As Variant
Dim shtcount As Long

Set Todate = Range("Sorttable[To Date]")
Set Charge = Range("Sorttable[Charge Type]")
Set Duedate = Range("Sorttable[Due Date]")

Sheets("Sort_Table").Select  'select worksheet

For Each cell In Charge  'loop to find charge type
  If Not IsEmpty(cell) Then
   C = cell.Value
   D = CType(C)
   
   Set Targ = cell.Offset(, 3)
   Set Ddate = cell.Offset(, 2)
   Set Charge_Amount = cell.Offset(, 4)
   Targdate = Targ.Value
   EDP = Ddate.Value
   Cash = Charge_Amount.Value
   Backyear = False
   
    If D = "Cash" Then
               
      Cashmonth = FM(EDP)
       TY = FY(EDP)
       EDPM = FM(EDP) + (FM(EDP) - 1)
       Cashcol = 28 + EDPM
       If Cashmonth = 0 Then Backyear = True
      
       If Backyear = True Then TY = FY(EDP) - 1
       If Backyear = True Then Cashcol = 51
    
       Worksheets(TY).Cells(16, Cashcol).End(xlUp).Offset(1, 0).Value = Cash
       Worksheets(TY).Cells(16, Cashcol - 1).End(xlUp).Offset(1, 0).Value = EDP

‘Elseif
    ‘Another bit of unrelated code that works
‘Elseif
    ‘Another bit of unrelated code that works
‘Elseif
    ‘Another bit of unrelated code that works

‘Else 
    ‘Another bit of unrelated code that works

Worksheets("Sort_Table").Select
End if
End if
Next Cell
End Sub

Solution

Just define TY as String not Variant. Tim explained what's going on.

TY As String
TY = FY(EDP) - 1 ' TY will stay String and accept a new value which will be converted to String