Thread: Auto Fill

Hi,

In the 'Project Schedule' example sheet, I need the ability to insert an 'F' or a 'T' in a cell and have the 26 cells following it automatically fill with a '1' to create a 28 day working schedule followed by a matching 'F' or a 'T' at the end.

Example: F 1 1 1 1 1 1 1 1 1 F or T 1 1 1 1 1 1 1 1 1 T

Thanks

Not pretty but does the below work for you ?

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngInterest As Range, rngFormulas As Range, rngArea As Range, rngRow As Range
Dim lngResize As Long
On Error Resume Next
Set rngInterest = Intersect(Target, Range("E15:BE43"))
On Error GoTo ExitPoint
If rngInterest Is Nothing Then GoTo ExitPoint
Application.EnableEvents = False
Application.Undo
On Error Resume Next
Set rngFormulas = Range("E15:BE43").SpecialCells(xlCellTypeFormulas)
On Error GoTo ExitPoint
If Not Intersect(rngInterest, rngFormulas) Is Nothing Then
    If MsgBox("This Action Will Override Formulas - Undo", vbYesNo, "Undo?") = vbYes Then GoTo ExitPoint
Else
    Application.Undo
    If Application.WorksheetFunction.CountA(rngInterest) Then
        For Each rngRow In rngInterest.Rows
            If UCase(rngRow(1).Value) = "T" Or UCase(rngRow(1).Value) = "F" Then
                With rngRow(1)
                    lngResize = Range("BE15").Column - rngRow(1).Column
                    If lngResize Then
                        .Offset(, 1).Resize(, Application.WorksheetFunction.Min(28, lngResize)) = 1
                        If lngResize > 28 Then .Offset(, 29).Value = .Value
                    End If
                End With
            End If
        Next rngRow
    End If
End If
ExitPoint:
Set rngFormulas = Nothing
Set rngInterest = Nothing
Application.EnableEvents = True
End Sub

to insert, right click on Project Schedule sheet, select View Code and paste above into resulting window - in macro enabled file thereafter it should do what you want I think

Code is setup such that you are warned if you're overriding formulae - you should be able to update multiple rows simultaneously... eg highlight N23, P28, S33 and type T and press CTRL + ENTER - all three should update
(1's will stop at end of matrix should you update near end of month etc)