Vfrdtyky

I have a loop macro that changes a row of dates based on cells I have selected. Is there an easy way to format these cells without complicating the code. My company has policies around keeping Macros as simple as possible.

I have tried the below at the top of the code but this formats the whole sheet, not the relevant cells.

cells.HorizontalAlignment = xlRight

cells.NumberFormat = "mmm-yy"

I can do it via the below but it adds 100 lines to the code

cell.Offset(0, 0) = "1-Jul-19"

cell.Offset(0, 0).HorizontalAlignment = xlRight

cell.Offset(0, 0).NumberFormat = "mmm-yy"

Is there a way to do this on one line i.e.

cell.Offset(0, 0) = "1-Jul-19".HorizontalAlignment = xlRight.NumberFormat = "mmm-yy"

Help would be much appreciated.

Scott

**Sub CHANGE_MONTH_LOOP()

Dim rngMyRange As range

Dim cell As range

Set rngMyRange = Selection

For Each cell In rngMyRange.Cells

cell.Offset(0, 0) = "1-Jul-19"



cell.Offset(0, 1) = "1-Aug-19"



cell.Offset(0, 2) = "1-Sep-19"



Next cell



End Sub**

Back again, so if in @alowflyingpig and your boss' world less lines necessarily means more simple please see below a code that does exactly what your "full code" does... but in less lines...

Sub DATE_MONTHLY_LOOP()

Dim cell As Range, i As Integer

For Each cell In Selection.Cells: For i = 0 To 11: With cell.Offset(0, i): .Value = DateAdd("m", 1 + i, 43617): .HorizontalAlignment = xlRight: .NumberFormat = "mmm-yy": End With: Next i:    With cell.Offset(0, 12): .Value = "FY20 TOTAL": .ColumnWidth = 11.3: .HorizontalAlignment = xlRight: .NumberFormat = "mmm-yy": End With: Next cell

End Sub

PICTURE LINK

This is what the sheet looks like. Basically I am using find / select and then on the highlighted cells I am running my macro. I am deliberately doing this one sheet at a time as there are some exceptions. The macro populates the adjacent cells using the cell.offset function.

Ideally all I am trying to do is get these three commands on one line

cell.Offset(0, 0).HorizontalAlignment = xlRight

cell.Offset(0, 0).NumberFormat = "mmm-yy"

cell.Offset(0, 0) = "1-Jul-19"

i.e.
cell.Offset(0, 0).HorizontalAlignment = xlRight.NumberFormat = "mmm-yy".value = "1-Jul-19"

Anyone know if that can be done?

FULL CODE:

Sub DATE_MONTHLY_LOOP()



Dim rngMyRange As range

Dim cell As range



Set rngMyRange = Selection



For Each cell In rngMyRange.Cells



cell.Offset(0, 0) = "1-Jul-19"

cell.Offset(0, 0).HorizontalAlignment = xlRight

cell.Offset(0, 0).NumberFormat = "mmm-yy"





cell.Offset(0, 1) = "1-Aug-19"

cell.Offset(0, 1).HorizontalAlignment = xlRight

cell.Offset(0, 1).NumberFormat = "mmm-yy"





cell.Offset(0, 2) = "1-Sep-19"

cell.Offset(0, 2).HorizontalAlignment = xlRight

cell.Offset(0, 2).NumberFormat = "mmm-yy"



cell.Offset(0, 3) = "1-Oct-19"

cell.Offset(0, 3).HorizontalAlignment = xlRight

cell.Offset(0, 3).NumberFormat = "mmm-yy"



cell.Offset(0, 4) = "1-Nov-19"

cell.Offset(0, 4).HorizontalAlignment = xlRight

cell.Offset(0, 4).NumberFormat = "mmm-yy"



cell.Offset(0, 5) = "1-Dec-19"

cell.Offset(0, 5).HorizontalAlignment = xlRight

cell.Offset(0, 5).NumberFormat = "mmm-yy"



cell.Offset(0, 6) = "1-Jan-20"

cell.Offset(0, 6).HorizontalAlignment = xlRight

cell.Offset(0, 6).NumberFormat = "mmm-yy"



cell.Offset(0, 7) = "1-Feb-20"

cell.Offset(0, 7).HorizontalAlignment = xlRight

cell.Offset(0, 7).NumberFormat = "mmm-yy"



cell.Offset(0, 8) = "1-Mar-20"

cell.Offset(0, 8).HorizontalAlignment = xlRight

cell.Offset(0, 8).NumberFormat = "mmm-yy"



cell.Offset(0, 9) = "1-Apr-20"

cell.Offset(0, 9).HorizontalAlignment = xlRight

cell.Offset(0, 9).NumberFormat = "mmm-yy"



cell.Offset(0, 10) = "1-May-20"

cell.Offset(0, 10).HorizontalAlignment = xlRight

cell.Offset(0, 10).NumberFormat = "mmm-yy"



cell.Offset(0, 11) = "1-Jun-20"

cell.Offset(0, 11).HorizontalAlignment = xlRight

cell.Offset(0, 11).NumberFormat = "mmm-yy"



cell.Offset(0, 12) = "FY20 TOTAL"

cell.Offset(0, 12).ColumnWidth = 11.3

cell.Offset(0, 12).HorizontalAlignment = xlRight

cell.Offset(0, 12).NumberFormat = "mmm-yy" 



Next cell



End sub

Sub test()





    Dim LastRow As Long

    Dim irow As Long

    Dim jrow As Long

    Dim StartCol As Long

    Dim StartRow As Long







    For Each ws In ActiveWorkbook.Worksheets

        With ws.Select

            LastRow = ws.Range("G" & Rows.Count).End(xlUp).Row



            Set StartDate = Cells.Find(What:="Jul-17", After:=ws.Range("A1"), LookIn:=xlValues _

                        , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)

            StartCol = StartDate.Column

            StartRow = StartDate.Row



                For irow = StartRow To LastRow

                    Set Rng = ws.Range(Cells(irow, StartCol), Cells(irow, StartCol + 11))

                        For Each Cell In Rng

                            Cell.Value = DateAdd("yyyy", 1, Cell)

                        Next Cell



                    Rng.HorizontalAlignment = xlRight

                    Rng.NumberFormat = "mmm-yy"



                    irow = irow + 2

                Next irow



                For jrow = StartRow To LastRow

                    Set Rng = ws.Range(Cells(jrow, StartCol + 12), Cells(jrow, StartCol + 12))

                        For Each Cell In Rng

                            Cell.Value = "FY19 Total"

                        Next Cell

                    jrow = jrow + 2

                Next jrow

        End With

    Next ws





End Sub