Good night!
I would put a formula column "P".
if the cell in column "M" is different then the empty cell "P2" = MONTH (M2).
the formula there will be dragged down until no more data in the cells of column "M".
See the attachment.
This forum has been moved to TeachExcel.com
Ask all future questions in the New Excel Forum.
ExcelKey
Option Explicit
Const FirstDataRow As Long = 2
Const DateCol As String = "M"
Const ResultCol As String = "P"
Public Sub WriteMonth()
Dim R As Long
Dim D As Variant
With ActiveSheet
For R = FirstDataRow To LastRow("M")
D = .Cells(R, DateCol).Value
If Len(D) And IsDate(D) Then
.Cells(R, ResultCol).Value = Month(D)
End If
Next R
End With
End Sub
Private Function LastRow(ByVal Col As String) As Long
LastRow = ActiveSheet.Cells(65536, Col).End(xlUp).Row
End Function
Option Explicit ' every variable must be explicitly named in the following code
Const FirstDataRow As Long = 2
Const DateCol As String = "M"
Const ResultCol As String = "P"
Public Sub WriteMonth()
Dim R As Long
Dim D As Variant
With ActiveSheet
' pick each of the rows in the active worksheet, one at a time,
' starting from FirstDataRow until the row found in the Function 'LastRow'
' Let the number of that row be known as R
For R = FirstDataRow To LastRow("M")
D = .Cells(R, DateCol).Value ' Let D have the same value as .Cells(R, DateCol)
' Only If D has a length AND D is a date Then execute the following lines of code
If Len(D) And IsDate(D) Then
' Let .Cells(R, ResultCol) take the value of the month of the date in variable D
.Cells(R, ResultCol).Value = Month(D)
End If
Next R ' loop here to look at the next R
End With
End Sub
Private Function LastRow(ByVal Col As String) As Long
' Let the return value of this function be equal to
' the row number of the first cell from the end in column 'Col'
' which is not empty
LastRow = ActiveSheet.Cells(65536, Col).End(xlUp).Row
End Function
Return to Macros and VBA Questions
Users browsing this forum: No registered users and 126 guests