Autofill Cells with Fixed Values

[rhmkrmi]

Hello, Is there a formula or code that can be used to auto populate certain texts with certain values?

In rows 15 to 26, I am populating column B odd number cells with FROM and even number cells with TO. When users insert rows in this range, I want the FROM and TO to be auto filled instead of users typing them in.

Thank you.

 

[JoeMo]

Here's some change event code you can try. Assumes that col B starting at B15 contains only the two entries "FROM" and "TO", and allows the range occupied by those values to grow or shrink as the user inserts or deletes rows within it. Also ensures that the two values always appear in pairs so the occupied range always ends with a "TO".

To install sheet code:
1. Right-click the worksheet tab you want to apply it to and choose 'View Code'. This will open the VBE window.
2. Copy the code below from your browser window and paste it into the white space in the VBE window.
3. Close the VBE window and Save the workbook. If you are using Excel 2007 or a later version do a SaveAs and save it as a macro-enabled workbook (.xlsm file extension).
4. Make sure you have enabled macros whenever you open the file or the code will not run.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim R As Range
Set R = Range("B15:B" & Cells(Rows.Count, "B").End(xlUp).Row)
If Not Intersect(R, Target) Is Nothing Then
    Application.EnableEvents = False
    With R(1).Resize(2, 1)
        .Value = Application.Transpose(Array("FROM", "TO"))
        .AutoFill Destination:=R, Type:=xlFillDefault
    End With
End If
If R(R.Rows.Count).Value = "FROM" Then R(R.Rows.Count).Offset(1, 0).Value = "TO"
Application.EnableEvents = True
End Sub

 

[Rick Rothstein]

Here's some change event code you can try. Assumes that col B starting at B15 contains only the two entries "FROM" and "TO", and allows the range occupied by those values to grow or shrink as the user inserts or deletes rows within it. Also ensures that the two values always appear in pairs so the occupied range always ends with a "TO".

To install sheet code:
1. Right-click the worksheet tab you want to apply it to and choose 'View Code'. This will open the VBE window.
2. Copy the code below from your browser window and paste it into the white space in the VBE window.
3. Close the VBE window and Save the workbook. If you are using Excel 2007 or a later version do a SaveAs and save it as a macro-enabled workbook (.xlsm file extension).
4. Make sure you have enabled macros whenever you open the file or the code will not run.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim R As Range
Set R = Range("B15:B" & Cells(Rows.Count, "B").End(xlUp).Row)
If Not Intersect(R, Target) Is Nothing Then
    Application.EnableEvents = False
    With R(1).Resize(2, 1)
        .Value = Application.Transpose(Array("FROM", "TO"))
        .AutoFill Destination:=R, Type:=xlFillDefault
    End With
End If
If R(R.Rows.Count).Value = "FROM" Then R(R.Rows.Count).Offset(1, 0).Value = "TO"
Application.EnableEvents = True
End Sub

You can replace what I highlighted in red with this to compact your code a little bit...

[{"FROM";"TO"}]

Also what I highlighted in blue could be replaced with this...

R(R.Rows.Count + 1)