CODEWITHSUNDEEP
excelvba
SBSP
SBSP

Reputation: 15

Autofil cell with the month number if adjacent cell contains date

I'm looking for a smoother solution for below code. The task is if column O is not empty, then check if AH is empty. If AH is not empty (then it contains a date) I need to get the number of the month from this date to column AI (right next to AH).

I'm new to coding and so far the below is what I've got but this doesn't seem the perfect solution as it is simply adding the formula to and I suppose this could be also done by a loop.

Many thanks in advance.

Sub d_month()

Dim r As Range
Dim LastRow As Long

With Application.ActiveSheet

    LastRow = .Cells(.Rows.Count, "O").End(xlUp).Row

    For Each r In .Range("O2:O" & LastRow)
        If r.Value <> "" Then
            r.Offset(0, 20).Value = "=IF(RC[-1]="""","""",MONTH(RC[-1]))"
        End If
    Next r
End With

End Sub

Upvotes: 1

Views: 44

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149305

There are various ways to do this. You can use Excel Formulas, Worksheet_Change or as shown below.

Few suggestions (not a hard and fast rule. Just my personal opinion).

  1. Keep the code simple and easy to understand.
  2. Avoid using Offset unless and until it is really important. This way you will know which cell is being handled just by looking at it. With offset, you will have to count and ensure that it is the right cell.
  3. Use simple For Loops as shown below.

Is this what you are trying?

Option Explicit

Sub Sample()
    Dim ws As Worksheet
    Dim lRow As Long
    Dim i As Long

    '~~> Change this to relevant sheet
    Set ws = Sheet1

    With ws
        lRow = .Range("O" & .Rows.Count).End(xlUp).Row

        For i = 2 To lRow
            '~~> Check if O and AH are not empty
            If Len(Trim(.Range("O" & i).Value)) <> 0 And _
               Len(Trim(.Range("AH" & i).Value)) <> 0 Then
                '~~> Write to AI
                .Range("AI" & i).Value = Month(.Range("AH" & i).Value)
            End If
        Next i
    End With
End Sub

Upvotes: 1

Related Questions