Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Page 1 of 2 1 2 LastLast
Results 1 to 10 of 11

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

  1. #1
    Join Date
    29th December 2011
    Posts
    4

    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 at 09:35. Reason: revise thread title

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    8th March 2011
    Location
    Australia
    Posts
    1,198

    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
    VB:
    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.




  3. #3
    Join Date
    23rd April 2007
    Posts
    3,452

    Re: Repeating cells in a series?

    A non looping approach
    VB:
    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 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    29th December 2011
    Posts
    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.

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    19th January 2007
    Location
    South Carolina, USA
    Posts
    9,206

    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

  6. #6
    Join Date
    29th December 2011
    Posts
    4

    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

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    19th January 2007
    Location
    South Carolina, USA
    Posts
    9,206

    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

  8. #8
    Join Date
    29th December 2011
    Posts
    4

    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!!!

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    8th March 2011
    Location
    Australia
    Posts
    1,198

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

    To save you cut and pasting you could amend the code to
    VB:
    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
    VB:
    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.




  10. #10
    Join Date
    4th April 2011
    Posts
    13

    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 .

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Replies: 2
    Last Post: August 5th, 2011, 23:48
  2. sum based on series of repeating values
    By mike_corban in forum EXCEL HELP
    Replies: 2
    Last Post: February 21st, 2011, 14:08
  3. Repeating Numeric Series For Day Hours
    By Yellowlion in forum EXCEL HELP
    Replies: 6
    Last Post: March 21st, 2008, 16:57
  4. Delete Cells With Repeating Values
    By Gammazoni in forum EXCEL HELP
    Replies: 11
    Last Post: November 27th, 2007, 08:12
  5. Replies: 10
    Last Post: April 21st, 2006, 05:46

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno