[Urgent] Problems about looping in the following program

Hi all,

I am quite new to excel VBA programming, but I need to write a program to extract the month from the string "15w24" where "15" represents year 2015 and "24" represents the 24th week, starting from the first week of January (where first week of January in 2015 is represented as 15w01), I have two buttons, the first button is called commandbutton1, where once click it should convert the 15w24 into 6 (because 24th week is in June, just say) and the second button is called commandbutton2, where once click, it should extract 15(that's the year) from 15w24, and the aim is to once click these two buttons, all data from the row of 8, column AN should be stored in AO column (for month) and AP column(for year), here is my program:

Public Function MonthFromDt(s As String) As Integer
Dim yr As Integer, wk As Integer, d As Date, k As Integer

    For k = 8 To 2068
        If s <> "TBC" Then
            ary = Split(s, "w")
            yr = CInt(ary(0)) + 2000
            wk = ary(1)
            MonthFromDt = Month(DateSerial(yr, 1, 1) + 7 * (wk - 1))
            Range("AO" & CStr(k)) = MonthFromDt   
        Else
            Range("AO" & CStr(k)).Value = "TBC"    
        End If
    Next k

End Function


Public Function Year(s2 As String)
Dim y1 As String, y2 As String

If s2 <> "TBC" Then
    y1 = s2
    Year = Left(y1, 2)
    Range("AP8:AP2068").Value = Year
Else
    Range("AP8:AP2068").Value = "TBC"
End If

End Function

Private Sub CommandButton1_Click()
Dim i As Integer

    For i = 8 To 2068
        MonthFromDt (Range("AN" & CStr(i)))
    Next i

End Sub

Private Sub CommandButton2_Click()
Dim j As Integer

    For j = 8 To 2068
        Year (Range("AN" + CStr(j)))
    Next j
End Sub

It stuck into a loop, and please help me to solve the problem, it's quite urgent
Thank you so much!

Range("AO" & CStr(k)) = MonthFromDt
Same as Range("AO" & CStr(k)) = Call MonthFromDt

that "Call" raises a missing parameter error.

That is the wrong use for a Function because you are not using its returned Value. But you are modifying a worksheet with it.

Option Explicit

Public Sub MonthFromDt(Cel As Range)
Dim yr As Variant, wk As Integer
Dim Ary As Variant
Dim m As Variant
  If Cel.Value <> "TBC" Then
      Ary = Split(Cel, "w")
      yr = CInt(Ary(0)) + 2000
      wk = Ary(1)
      m = Month(DateSerial(yr, 1, 1) + 7 * (wk - 1))
   Else
      m = "TBC"
      yr = "TBC"
  End If
           
  Cel.Offset(0, 1) = m
  Cel.Offset(0, 2) = yr

End Sub

Private Sub CommandButton1_Click()
Dim i As Integer

    For i = 8 To 2068
        MonthFromDt Range("AN" & CStr(i))
    Next i

End Sub

1. A basic function is meant to return a single value to a single cell in the worksheet (the cell containing the function).
2. The function must have a line that assigns the calculated value to the cell containing the function. In the function CubeRt()

Public Function CubeRt(ByVal Radicand as double) As Double
      CubeRt = Radicand ^ (1/3)    <<<< This line returns the value to the cell
End Function

3. In your function, you have the line: MonthFromDt = Month(DateSerial(yr, 1, 1) + 7 * (wk - 1)) which should return the calculated month to the cell.

The function should have ended with that line; instead, it is followed by the line:
Range("AO" & CStr(k)) = MonthFromDt

You are trying to write the month to a series of cells (2060 cells!) in the worksheet using a loop.
What it is actually doing is trying to call the function from within the function itself (it's called recursion).
The function requires a string to be passed to the variable "s" but you are passing nothing so the program crashes.

The function should look like:

Public Function MonthFromDt(ByVal s As String) As Long
    Dim yr As Integer, wk As Integer, d As Date, k As Integer
    If s <> "TBC" Then
        ary = Split(s, "w")
        yr = CInt(ary(0)) + 2000
        wk = ary(1)
        MonthFromDt = Month(DateSerial(yr, 1, 1) + 7 * (wk - 1))
    End If
End Function

I don't know why you need the vb function, it can be done directly in the cell using:

=DATE(LEFT(A1,2)+2000,1,1)+7*(RIGHT(A1,2)-1)