VBA - Do a command in cell until adjacent cell is empty
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: VBA - Do a command in cell until adjacent cell is empty

  1. #1
    New Member
    Join Date
    Feb 2018
    Posts
    22
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA - Do a command in cell until adjacent cell is empty

    Hello -

    I am trying to figure out how to copy and paste cell (for example) A1's content into cell A2, A3 and on but only if cell B2, B3 etc has content in it, otherwise, stop the copy/pasting. I also need this to be a relative reference. Please help TY so much




    [IMG][/IMG]
    [IMG][/IMG]

  2. #2
    Board Regular
    Join Date
    Dec 2017
    Location
    UK
    Posts
    430
    Post Thanks / Like
    Mentioned
    17 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA - Do a command in cell until adjacent cell is empty

    This should do it:
    Code:
    Sub test()
    
    Dim outarr As Variant
    Arow1 = Cells(1, 1)
    lastrow = Cells(Rows.Count, "B").End(xlUp).Row
    colb = Range(Cells(1, 2), Cells(lastrow, 2))
    For i = 1 To lastrow
     If colb(i, 1) = "" Then
      Exit For
     End If
    Next i
    
    
    ReDim outarr(1 To i - 2, 1 To 1)
    For j = 1 To i - 2
     outarr(j, 1) = Arow1
    Next j
    Range(Cells(2, 1), Cells(i - 1, 1)) = outarr
    
    
    End Sub
    note I haven't done it using copy and paste which is very slow, I have used varinat arrays which is a much faster way of doing it.

  3. #3
    New Member
    Join Date
    Feb 2018
    Posts
    22
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA - Do a command in cell until adjacent cell is empty

    I will try this first thing tomorrow and will let you know how it goes. Thanks so much!

  4. #4
    New Member
    Join Date
    Feb 2018
    Posts
    22
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA - Do a command in cell until adjacent cell is empty

    Quote Originally Posted by offthelip View Post
    This should do it:
    Code:
    Sub test()
    
    Dim outarr As Variant
    Arow1 = Cells(1, 1)
    lastrow = Cells(Rows.Count, "B").End(xlUp).Row
    colb = Range(Cells(1, 2), Cells(lastrow, 2))
    For i = 1 To lastrow
     If colb(i, 1) = "" Then
      Exit For
     End If
    Next i
    
    
    ReDim outarr(1 To i - 2, 1 To 1)
    For j = 1 To i - 2
     outarr(j, 1) = Arow1
    Next j
    Range(Cells(2, 1), Cells(i - 1, 1)) = outarr
    
    
    End Sub
    note I haven't done it using copy and paste which is very slow, I have used varinat arrays which is a much faster way of doing it.

    Hey! This code works! However it only works for the specific area of cell 1,1. I have several groupings of this same scenario that start at for example A10, A21

  5. #5
    Board Regular
    Join Date
    Dec 2017
    Location
    UK
    Posts
    430
    Post Thanks / Like
    Mentioned
    17 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA - Do a command in cell until adjacent cell is empty

    How do you plan on identifying where the next grouping starts? Do you want it to done automatically by running down column A to find the next cell with something in it? or are you going to enter the starting cell manually, either by selecting it or entering it in a message box? And what happens if the data in column B continues past the next value. Your inital requirements were very good and quite clear, your new requirements are not so clear.
    The code currently just keeps going until it runs out of data in column B regardless of what is in column A. This appears to me to be what you asked for originally.

  6. #6
    New Member
    Join Date
    Feb 2018
    Posts
    22
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA - Do a command in cell until adjacent cell is empty

    ^


    Hello! The next grouping will be identified by the user manually UNLESS there is a way to run down Column A till it hits a number then it refers to column B whether or not to replicate A's (whichever row it is) new number. Currently The macro works wonderfully when the data set starts at A1/B1. But when I manually shift down to the next group, the macro does not run.

    Let me know if I am making sense =) TY for your help with this.

    [IMG][/IMG]

  7. #7
    Board Regular
    Join Date
    Dec 2017
    Location
    UK
    Posts
    430
    Post Thanks / Like
    Mentioned
    17 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA - Do a command in cell until adjacent cell is empty

    try this code:
    Code:
    Dim outarr As Variant
    lastrow = Cells(Rows.Count, "B").End(xlUp).Row
    cola = Range(Cells(1, 1), Cells(lastrow, 1))
    colb = Range(Cells(1, 2), Cells(lastrow, 2))
    Arow1 = cola(1, 1)
    
    
    For i = 1 To lastrow
     
     If colb(i, 1) = "" Then
       Arow1 = colb(i + 1, 1)
     Else
       cola(i, 1) = Arow1
     End If
    Next i
    
    
    
    
    Range(Cells(1, 1), Cells(lastrow, 1)) = cola

  8. #8
    New Member
    Join Date
    Feb 2018
    Posts
    22
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA - Do a command in cell until adjacent cell is empty

    ^^


    ALMOST!! I was trying to change your code slightly to accommodate but to no avail. What your code is doing is after the initial successful repetition of the 4 digit number in ColA (example A1:A5), the code then, for the subsequent groupings takes the first new number from ColB and replicates that in ColA, rather than taking the new number from ColA and replicating it - The 4 digit numbers in colA should be the ones replicated.

    So CLOSE, I can taste it!

    [IMG][/IMG]

  9. #9
    Board Regular
    Join Date
    Dec 2017
    Location
    UK
    Posts
    430
    Post Thanks / Like
    Mentioned
    17 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA - Do a command in cell until adjacent cell is empty

    sorry typo:
    Code:
    Dim outarr As Variantlastrow = Cells(Rows.Count, "B").End(xlUp).Row
    cola = Range(Cells(1, 1), Cells(lastrow, 1))
    colb = Range(Cells(1, 2), Cells(lastrow, 2))
    Arow1 = cola(1, 1)
    
    
    For i = 1 To lastrow
     
     If colb(i, 1) = "" Then
       Arow1 = cola(i + 1, 1)
     Else
       cola(i, 1) = Arow1
     End If
    Next i
    
    
    
     Range(Cells(1, 1), Cells(lastrow, 1)) = cola

  10. #10
    New Member
    Join Date
    Feb 2018
    Posts
    22
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA - Do a command in cell until adjacent cell is empty

    Quote Originally Posted by offthelip View Post
    sorry typo:
    Code:
    Dim outarr As Variantlastrow = Cells(Rows.Count, "B").End(xlUp).Row
    cola = Range(Cells(1, 1), Cells(lastrow, 1))
    colb = Range(Cells(1, 2), Cells(lastrow, 2))
    Arow1 = cola(1, 1)
    
    
    For i = 1 To lastrow
     
     If colb(i, 1) = "" Then
       Arow1 = cola(i + 1, 1)
     Else
       cola(i, 1) = Arow1
     End If
    Next i
    
    
    
     Range(Cells(1, 1), Cells(lastrow, 1)) = cola

    Oh man, this is awesome, . I understand about 3% of your code but it's working like a charm. Thank you so much for taking the time to help me with this.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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