Hey all
Im using VBA to display the dates of the first of the month for a number of months determined by the user. This is not a problem, The rather embarresing trouble I am actually having is with my nested DO loop! It wont stop running despite trying different VBA DO and FOR styles... here is my code (ps i know the code in the middle of this is bad but thats because if I correct it my computer feels the effects of a never ending do loop in Excel - and dies)
The reason I use a nested loop is to print the dates row by row. There will eventually be 4 columns but at the moment I am focusing on just the middle 2.Code:Public Sub Auto_Date() Dim x, i, j, Default, months_req, temporary As Integer Dim Title As String Dim Start, Ending As Date Message = "Enter a value between 1 and 6" ' Set prompt. Title = "How many months required?" ' Set title. Default = "3" ' Set default. months_req = InputBox(Message, Title, Default, 800, 500) On Error GoTo errorhandler If months_req > 6 Or months_req < 1 Then months_req = 3 'error trap Application.ScreenUpdating = False i = 1 Do While (i <= months_req) j = 1 Do While (j <= 4) If (j = 2 And i = 1) Then Cells(j, i).FormulaR1C1 = "=DATE(YEAR(TODAY()),MONTH(TODAY()), 1)" End If If (j = 2 And i > 1) Then Cells(j, i) = "=R[-1]C[1]" End If If (j = 3) Then Cells(j, i).FormulaR1C1 = "=DATE(YEAR(RC[-1]), MONTH(RC[-1]) + 1, 1)" End If Cells(10, i) = i Cells(11, i) = j j = j + 1 Loop i = i + 1 Loop errorhandler: Exit Sub End Sub
Again the only problem I have with this at the mo is that the loops wont stop.
This bit of code
I put in to display the values of i and j... according to this i increases (1, 2, 3, 4, 5...) whereas j is always equal to 4! (4, 4, 4, 4, 4 ...)Code:Cells(10, i) = i Cells(11, i) = j
What is up!?!?! im hardly the best programmer, but not exactly new - Ive not had trouble like this with a loop afor a long while!
Cheers in advance
Tom
Bookmarks