Odtnhj

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