Increment months in next available column, but multiple rows

user_10590571 0
Viewed 103

I have VBA code which increments dates in the active cell to the next available column.

Dim lngLastCol As Long, lngRow As Long
lngRow = ActiveCell.Row
lngLastCol = Cells(lngRow, Columns.Count).End(xlToLeft).Column

If IsDate(Cells(lngRow, lngLastCol)) Then
    With Cells(lngRow, lngLastCol + 1)
        .Value = DateAdd("M", 1, CDate(Cells(lngRow, lngLastCol)))
        .NumberFormat = Cells(lngRow, lngLastCol).NumberFormat
    End With
End If

Instead of incrementing the month (and year) on the active row I am currently clicked on, I want to update the months in certain fixed rows i.e. Row 3, 17 and 42 (all in the same column).

vba excel
2 Answers

I dont understand exactly what to you want but you can use the below code and if you want more adjustment let me know.

Option Explicit

Sub test()

    Dim lngLastCol As Long, lngRow As Long

    lngRow = ActiveCell.Row
    lngLastCol = Cells(lngRow, Columns.Count).End(xlToLeft).Column

    If IsDate(Cells(lngRow, lngLastCol)) Then
        With Union(Cells(3, lngLastCol + 1), Cells(17, lngLastCol + 1), Cells(42, lngLastCol + 1))
            .Value = DateAdd("M", 1, CDate(Cells(lngRow, lngLastCol)))
            .NumberFormat = Cells(lngRow, lngLastCol).NumberFormat
        End With
    End If

End Sub
user_67744720

Another approach, loop one column to the last row (in this case 250). In the second If formula you set which rows to add new columns to. So if this statement is true Cells(i, 2).Row = 3 (current row we loop is 3) then add a new column.

Therefore we replace the active row with a loop:

lngRow = ActiveCell.Row -> lngRow = Cells(i, 2).Row

The For i loop will from row 3 to row 250.

Sub ColumnsAdd()

Dim lngLastCol As Long, lngRow As Long, i As Long

For i = 3 To 250 'Loop from row 3 to 250
    If Cells(i, 2).Row = 3 Or Cells(i, 2).Row = 17 Or Cells(i, 2).Row = 42 Then 'If any of the rows is 3, 17 or 42 then go and add new column
        lngRow = Cells(i, 2).Row
        lngLastCol = Cells(lngRow, Columns.Count).End(xlToLeft).Column
            If IsDate(Cells(lngRow, lngLastCol)) Then
            With Cells(lngRow, lngLastCol + 1)
                .Value = DateAdd("M", 1, CDate(Cells(lngRow, lngLastCol)))
                .NumberFormat = Cells(lngRow, lngLastCol).NumberFormat
            End With
            End If
    End If
Next i
End Sub
user_45525290
Related