Announcement

Collapse
No announcement yet.

Fill cells down column x number of time with repeating numbers

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Fill cells down column x number of time with repeating numbers



    I need help finding a way to fill and then repeat cell values in a collum in a series. I need to make an entry then fill the next 26 cells with the same value then go to the next value in the series and repeat the new value 26 more times and keep repeating this 2000 times. Copy and paste will take days. Below is a small version of what i need completed

    1
    1
    1
    1
    2
    2
    2
    2
    3
    3
    3
    3
    4
    4
    4
    4
    all the way up to about 2225
    Last edited by AAE; December 29th, 2011, 10:35. Reason: revise thread title

  • #2
    Re: Repeating cells in a series?

    If you mean from the column above take the first value and copy it to a column 26 times then take the next value and copy it 26 times all the way down the column - Assuming the values you listed are in column A
    Code:
    Option Explicit
    
    
    Sub repColEntry()
    Application.ScreenUpdating = False
    Dim t As Long, x As Long
    x = 1
    For t = 1 To Cells(Rows.Count, "A").End(xlUp).Row
    Cells(t, 1).Copy
    Range("B" & x & ":B" & x + 25).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
            False, Transpose:=False
    x = x + 26
    Next
    Application.ScreenUpdating = True
    End Sub
    Regards
    Anthony

    ​​​You have your way. I have my way. As for the right way, the correct way, and the only way, it does not exist.



    Comment


    • #3
      Re: Repeating cells in a series?

      A non looping approach
      Code:
      Dim maxNumber as Long, numberOfRepeats as Long
      
      numberOfRepeats = 26
      maxNumber = 2225
      
      With Range("A1").Resize(numberOfRepeats * maxNumber, 1)
          .FormulaR1C1 = "=INT((ROW()-1)/" & CStr(numberOfRepeats) & ")"
          .Value = .Value
      End With

      Comment


      • #4
        Re: Fill cells down column x number of time with repeating numbers

        Wow, thank you both for the quick responce and problem solving! Only one more question, what do I do with the coding you have provided. sorry so inexperenced.

        Comment


        • #5
          Re: Fill cells down column x number of time with repeating numbers

          The code provided is intended to go into a Standard Module (as opposed to a Class Module.

          To add the code

          1. Press Alt + F11 to open the VB Editor
          2. Menu > Insert > Module (not class module)
          3. Copy and paste the above code into the code pane
          4. Make any necessary adjustments to sheet/range references
          5. Alt + Q to close the Vb Editor

          To run the macro:
          Alt + F8 to invoke the Run Macro Dialog
          Choose the macro and click on Run
          AAE
          ----------------------------------------------------

          Forum Rules | Message to Cross Posters | How to use Tags

          Comment


          • #6
            Re: Fill cells down column x number of time with repeating numbers

            i dont think the macro is saving. its not listed in the dialog box that ALT + F8 provides, everything opens and closes as you said it would but appears its not adding the marco to the list to choose from

            Comment


            • #7
              Re: Fill cells down column x number of time with repeating numbers

              I forgot to mention that with Mike's code you must give the macro a name.

              So, follow the steps above, but before step-3, in the code pane type the following or something similar and press enter key

              Sub Repeat_Numbers

              VBA will automatically add "End Sub"

              If using Mike's code copy and paste the code just after the Sub Repeat_Numbers line.

              Anthony's code may copied and pasted in full without further need to manipulate VBA.

              If you are using Excel 2007+ you must save the file as a macro-enabled file type (.xlsm).
              AAE
              ----------------------------------------------------

              Forum Rules | Message to Cross Posters | How to use Tags

              Comment


              • #8
                Re: Fill cells down column x number of time with repeating numbers

                i got the looping code to work perfect, thank you, only thing is that it puts the new series in the next row. but nothing a quick copy and paste cant fix. THANK YOU!!!

                Comment


                • #9
                  Re: Fill cells down column x number of time with repeating numbers

                  To save you cut and pasting you could amend the code to
                  Code:
                  Option Explicit
                   
                  Sub repColEntry()
                      Application.ScreenUpdating = False
                      Dim t As Long, x As Long
                      x = 1
                      For t = 1 To Cells(Rows.Count, "A").End(xlUp).Row
                          Cells(t, 1).Copy
                          Range("B" & x & ":B" & x + 25).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
                          False, Transpose:=False
                          x = x + 26
                      Next
                     Range("B1:B" & Cells(Rows.Count, "B").End(xlUp).Row).Copy
                     Range("A1").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
                          False, Transpose:=False
                      Range("B1:B" & Cells(Rows.Count, "B").End(xlUp).Row).Clear
                  End Sub
                  As a general rule if you can avoid loops do so. I would recommend you try Mike's code
                  Code:
                  Sub copyRows()
                  Sub copyRows()
                  Dim maxNumber As Long, numberOfRepeats As Long
                   
                  numberOfRepeats = 26
                  maxNumber = 2225
                   
                  With Range("A1").Resize(numberOfRepeats * maxNumber, 1)
                      .FormulaR1C1 = "=INT((ROW()-1)/" & CStr(numberOfRepeats) & ")"
                      .Value = .Value
                  End With
                  End Sub
                  No loops, shorter code!!!
                  Regards
                  Anthony

                  ​​​You have your way. I have my way. As for the right way, the correct way, and the only way, it does not exist.



                  Comment


                  • #10
                    Re: Fill cells down column x number of time with repeating numbers

                    what code do i use if i want to add the numbers 1 to 100 in column A repeating each number 5 times . The above code does when i used was giving me the same value throughout . Sorry i am new to vba .

                    Comment


                    • #11


                      Re: Fill cells down column x number of time with repeating numbers

                      Absolutely No loop
                      Code:
                      Sub test()
                      [a1:a500] = [if(row(a1:a500),roundup(row(a1:a500)/5,0))]
                      End Sub

                      Comment

                      Working...
                      X