automate activecell values and offset function-CodePudding

'Please help me to automate the below code using input function, so that i can just type the number in the input box to get desired rows to be filled automatically.'

sub DAY()
GENERAL Macro


ActiveCell.Value = "DAY"

Select Case ActiveCell.Value

Case "DAY"
    ActiveCell.Resize(, 1).Value = "DAY"
    ActiveCell.Offset(, 1).Value = "OFF"
    ActiveCell.Offset(, 2).Value = "DAY"
    ActiveCell.Offset(, 3).Value = "OFF"
    ActiveCell.Offset(, 4).Value = "DAY"
    ActiveCell.Offset(, 5).Value = "OFF"
    ActiveCell.Offset(, 6).Value = "DAY"
    ActiveCell.Offset(, 7).Value = "OFF"
    ActiveCell.Offset(, 8).Value = "DAY"
    ActiveCell.Offset(, 9).Value = "OFF"
    ActiveCell.Offset(, 10).Value = "DAY"
    ActiveCell.Offset(, 11).Value = "OFF"
    ActiveCell.Offset(, 12).Value = "DAY"
    ActiveCell.Offset(, 13).Value = "OFF"


 End Select

 End Sub

'Please help me to automate the below code using input function, so that i can just type the number in the input box to get desired rows to be filled automatically.'

CodePudding user response：

I think you might want something like this?

Sub PopulateListByRows()
    Dim n As Long, a As Long, ArrTxt
    n = InputBox("How many days?")
    ArrTxt = Array("Day", "Off")
    For a = 1 To n
        ActiveCell.Offset(a - 1).Value = ArrTxt((a   1) Mod 2)
    Next
End Sub

Sub PopulateListByColumns()
    Dim n As Long, a As Long, ArrTxt
    n = InputBox("How many days?")
    ArrTxt = Array("Day", "Off")
    For a = 1 To n
        ActiveCell.Offset(0, a - 1).Value = ArrTxt((a   1) Mod 2)
    Next
End Sub

CodePudding user response：

you may use formulas

Sub Day()
    Dim n As Long
    n = CLng(InputBox("How many days?"))
    With ActiveCell.Resize(1, 2 * n)
        .FormulaR1C1 = "=IF(RC[-1]=""DAY"","OFF"",""DAY"")"
        .Value = .Value
    End With
End Sub