[SOLVED] dragging data

[marreco]

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.

[Sisyphus]

Hi,
The following code should do the job:

Code: Select all

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


Paste it into a normal code module and call it from Tools/Macro/Macros - WriteMonth & Run.

[marreco]

It was perfect, you should know all of VBA.
Is it asking too much if I could know what each line of code does, I would like to learn more.
Thank you very much!

Have a nice day!

[Sisyphus]

Hi,
Here is a commented version of the above code:

Code: Select all

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


[marreco]

Hi..

I really enjoyed the comments so I can learn a little.

Thank you and have a nice day.