Automate number entry in cells based on condition in Excel

Question

I have entries in columns as follows:

Col1  Col2  Col3  Col4  Col5
1      0     0      1    0

As you see, 1 repeats at every 3rd position after "1 in Col1". I want to write a code that if I type "4" then 1 should repeat at every 4th position...and if I write 2 then it should repeat at every 2nd position...

Example output: I type in a cell: "4"

Col1  Col2  Col3  Col4  Col5
1      0     0      0    1

Note: I dont know how to create tables in StackOverflow, any help there would be great too :(

Thanks!!

How long is it supposed to go for? – Davesexcel May 07 '18 at 15:20 — Davesexcel, May 07 '18 at 15:20

score 2 · Answer 1 · answered May 07 '18 at 15:20

2

put this in A1 and drag/copy over:

=IF(MOD(COLUMN(A:A)-1,$A$3)=0,1,0)

answered May 07 '18 at 15:20

Scott Craner

148,073
10
49
81

Vityata · Answer 2 · 2018-05-07T15:27:33.267

1

This is some example of a Sub with "optional parameters":

Public Sub RepeatMe(Optional repeatEach As Long = 3, Optional repeatLen As Long = 20)

    Dim myCell  As Range
    Cells.Clear

    Dim cnt As Long
    Range(Cells(1, 1), Cells(1, repeatLen)).Value = 0

    For cnt = 1 To repeatLen Step repeatEach
        Cells(1, cnt) = 1
    Next cnt

End Sub

To run it, go to the immediate window, write RepeatMe and press Enter.

edited May 07 '18 at 15:27

answered May 07 '18 at 15:22

Vityata

42,633
8
55
100

Shouldn't `repeatEach` be initialized to 4? – Joseph Wood May 07 '18 at 15:24
1

Nevermind my first comment... I see how the OP wants a dynamic approach. – Joseph Wood May 07 '18 at 15:26
@ScottCraner - I am usually ignoring the `0` by default. :) – Vityata May 07 '18 at 15:28

Automate number entry in cells based on condition in Excel

2 Answers2