[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:

Please Login or Register  to view this content.

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.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

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()

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.