VB code to automate a function for a variable range

[Ziggy8]

VB code to go to the end of the column:
In a Sheet called “Student data”, I have a column called “DOB” which is populated with a variable range of dates of birth of students. I’m unable to predict the number of populated cells in this column as the number of students in the capture will vary.


VB code to extract and paste extraction:
I need to extract just the month number of each date of birth held in the column “DOB”
=MONTH(DOB)
and paste these numbers into a column called “MONTH_No” on a new sheet called “Analysis”


VB code to automate a function down a column until blank value reached:
Once the MONTH column on the “Analysis” sheet is correctly populated I need to automate a function in the next column called "TOB" (term of birth) to interpret each Month number and trigger seasons such as Autumn, Spring or Summer.

Where MONTH column on Analysis sheet is (A2)
=IF(MONTH(A2)<=3,"Spring",IF(MONTH(A2)<=8,"Summer","Autumn"))

In VB how do I make the formula automatically travel down the MONTH column for each cell which holds a value, so as to return a season in the TOB column?
Any help would be much appreciated. Thanks in anticipation.

 

[mumps]

Assuming "DOB" is in column A, try:

Sub Ziggy8()
    Application.ScreenUpdating = False
    Dim LastRow1 As Long, LastRow2 As Long
    LastRow1 = Sheets("Student data").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim rng As Range
    For Each rng In Sheets("Student data").Range("A2:A" & LastRow1)
        Sheets("Analysis").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0) = Month(rng)
    Next rng
    LastRow2 = Sheets("Analysis").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Sheets("Analysis").Range("B2:B" & LastRow2).Formula = "=IF(RC[-1]<=3,""Spring"",IF(RC[-1]<=8,""Summer"",""Autumn""))"
    Application.ScreenUpdating = True
End Sub