Consulting

Results 1 to 9 of 9

Thread: Help to create repeat macro until blank cell found

  1. #1

    Help to create repeat macro until blank cell found

    I have data in an unknown number of rows
    I need to copy each row and paste it the number of times stated in in a corresponding cell.
    I have got this to work for an individual row but cannot get it to loop down the rows until it reaches a blank cell.
    This is the code I have to copy one row A2 contains the number of times to be repeated and should also be what is checked for being blank.

    Sub Copy_Paste_2()
    Dim I As Long
    For I = 1 To Range("A2")
    Range("B2:F2").Copy
    Range("I" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
    Next I
    End Sub

    This is my failed attempt at looping


    Sub FindEmptyCell_1()
    Range("A2").Select
    Do
    For I = 1 To Range("A2")
    Range("B2:F2").Copy
    Range("I" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
    Next I
    ActiveCell.Offset(1, 0).Select
    Loop Until IsEmpty(ActiveCell.Value)
    End Sub
    I really appreciate any help/advice given, am about to repeat the some macro 50 times and then link them together :/
    Last edited by Bob Phillips; 02-19-2015 at 06:54 AM. Reason: Added code tags

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,453
    Location
    Sub Copy_Paste_2()
    Dim numrows As Long
    Dim I As Long
        
        numrows = .Range("B2").End(xlDown).Row - 1
        
        For I = 1 To Range("A2")
        
            Range("B2:F2").Resize(numrows).Copy
            Range("I" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
        Next I
    End Sub
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  3. #3
    Thanks for replying, but it doesn't seem to work.
    It copies the rows but it does not repeat the rows for the value of the cell in column A of each row.

  4. #4
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,453
    Location
    How many times does it do it?
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  5. #5
    It just copies each row once.

  6. #6
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,453
    Location
    And what is in A2?
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  7. #7
    A2 contains the number of times B2:F2 need to be repeated, A3 contains the number of times B3:F3 need to be repeated and so on

  8. #8
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,453
    Location
    Yes, but is the value 1 or something else.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  9. #9
    A2 = 1 but this number can be different for each row

Posting Permissions

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