VBA Macro - For Loop with MonthName function in order to increment a variable

[ToBaCo]

Hello everyone,

first post in the community, I hope I will do this correctly

I have been trying to develop a macro that gives prices and quantities of material based on a user input (the first month [1 for January etc] and the amount of months required using the principle of a rolling 12 months [1 to 12]). In the workbook, I have a table with forecasted product quantity and price per month. The macro has access to this table and sums up information for the user defined period.

In order to find which cells to sum up in order to increment a newly created variable, I use the Find method which works fine while the number is between 1 and 12. As my user input is restricted to 12 months, I use an if statement that corrects for numbers greater than 12. Using debug.print, the returned month name is correct.

If j < 13 Then 'For monthname to work properly R = MonthName(j)


Else
R = MonthName(j - 12)


End If

In the following step I increment a newly created variable through a For loop. This works perfectly fine as long as the j from the For loop is not greater than 12. The code returns 0 quantities.. I have tried several changes in the code (iterating from last month to first month for order, doing the calculus in the if loop), I haven' t been able to make the code work.

First_Month = Val(InputBox("First month of order? (Use number between 1 and 12)", _
"1st Month Order"))


Months_Quantity = Val(InputBox("How many months in the order? (Use number between 1 (one month) and 12 (one year))", _
"Order Duration"))


TotalPrice = 0
TotalQuantity = 0
For j = LastMonth To First_Month Step -1

If j < 13 Then 'For monthname to work properly
R = MonthName(j)


Else
R = MonthName(j - 12)


End If

Set X = Range(Cells(1, 14), Cells(30, 14)).Find(what:="P_" & R, after:=Cells(1, 14), _
LookIn:=xlValues, lookat:=xlPart, searchorder:=xlByRows, searchdirection:=xlNext, MatchCase:=False, searchformat:=False)
XRow = X.Row

Set Z = Range(Cells(1, 14), Cells(30, 14)).Find(what:="Q_" & R, after:=Cells(1, 14), _
LookIn:=xlValues, lookat:=xlPart, searchorder:=xlByRows, searchdirection:=xlNext, MatchCase:=False, searchformat:=False)
ZRow = Z.Row

TotalPrice = TotalPrice + Cells(XRow, YColumn)
TotalQuantity = TotalQuantity + Cells(ZRow, YColumn)

Next j

Sheets(SheetName).Select
Cells(i, 4) = TotalPrice
Cells(i, 3) = TotalQuantity

Can someone please help me correct this problem when the loop uses a j greater than 12? This would be really kind!! Thank you very much guys

ToBaCo