Thanks Thanks:  0
Likes Likes:  0
Page 1 of 3 123 LastLast
Results 1 to 10 of 21

Thread: [VBA] Loop through a range in column A, copy data on the right according to a specific value in the looping range

  1. #1
    New Member
    Join Date
    Sep 2014
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default [VBA] Loop through a range in column A, copy data on the right according to a specific value in the looping range

    Hi all,

    I am working on a task and need a macro to loop through Column A which contains numbers and copy the data in Columns B & C with a specific number in column A (please see the table below)

    Column A Column B Column C
    1 a aa
    2 b bb
    2 b bb
    2 b bb
    2 b bb
    3 c cc
    3 c cc

    For example, I want to copy data in columns B & C with number 1 in column A and then paste in Columns D & E. Subsequently, the macro will copy data with number 2 then paste in Columns F & G and so forth.

    I have found a code that identifies the group with number 2s and copy related data. But this code stops at number 2 only. (Sorry I have googled this code and lost track of where I got it from.)

    Code:
    Sub ChooseRangeWithSpecificDataAndCopy()
    
    
       Dim Lrow As Integer
       Dim LColARange As String
       Dim LContinue As Boolean
       
       'Select Sheet1
       Sheets("Sheet1").Select
       Range("A2").Select
       
       'Initialize variables
       LContinue = True
       Lrow = 2
       
       'Loop through all column A values until a blank cell is found or value does not
       ' match cell A2's value
       While LContinue = True
          
          Lrow = Lrow + 1
          LColARange = "A" & CStr(Lrow)
          
          'Found a blank cell, do not continue
          If Len(Range(LColARange).Value) = 0 Then
             LContinue = False
          End If
          
          'Found first occurrence that did not match cell A2's value, do not continue
          If Range("A2").Value <> Range("A" & CStr(Lrow)).Value Then
             LContinue = False
          End If
          
          'Copy data from columns A - C
        Range("B2:C" & CStr(Lrow - 1)).Copy
          Range("E2").Select
          ActiveSheet.Paste
          
       Wend
       
       MsgBox "Copy has completed."
       
    End Sub
    As this task is urgent and I am not good at VBA, any help to solve this is greatly appreciated.

    Looking forward to your replies...

    Thank you.
    Last edited by raulmadrid; Sep 26th, 2014 at 01:39 AM.

  2. #2
    Board Regular
    Join Date
    May 2014
    Posts
    1,592
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: [VBA] Loop through a range in column A, copy data on the right according to a specific value in the looping range

    Not enough information provided. Please provide a dataset that has the desired results in it because your explination is not enough.

  3. #3
    Board Regular
    Join Date
    Sep 2014
    Location
    mesa, az
    Posts
    221
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: [VBA] Loop through a range in column A, copy data on the right according to a specific value in the looping range

    Will column A definitely be sorted ascending? Could there be any numbers missing? (like could it skip from 3 to 5?)

    I thought your explanation was good enough.

  4. #4
    Board Regular
    Join Date
    May 2014
    Posts
    1,592
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: [VBA] Loop through a range in column A, copy data on the right according to a specific value in the looping range

    I think i understand. Is this what he ment? The loop will check every cell in the A column to see if the value is "1". If it is 1, then it will put the B and C values in the D and E column. So the D and E column will have many rows that all had the A column value of 1. Then it will loop again and do the exact same thing for the next number which is 2. Only these will go in the F and G column. Let me know if I'm right on and I'll code it.

  5. #5
    Board Regular
    Join Date
    Sep 2014
    Location
    mesa, az
    Posts
    221
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: [VBA] Loop through a range in column A, copy data on the right according to a specific value in the looping range

    Never mind- didn't even need answer to those questions.

    Code:
    Function funStuff()
        Dim r As Range
        Set r = [a2]
        While r.Value <> ""
            If Val(r.Value) Then r.Offset(0, 1).Resize(1, 2).Copy r.Offset(0, 2 * r.Value + 1)
            Set r = r.Offset(1)
        Wend
    End Function

  6. #6
    Board Regular
    Join Date
    Sep 2014
    Location
    mesa, az
    Posts
    221
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: [VBA] Loop through a range in column A, copy data on the right according to a specific value in the looping range

    Quote Originally Posted by WarPiglet View Post
    I think i understand. Is this what he ment? The loop will check every cell in the A column to see if the value is "1". If it is 1, then it will put the B and C values in the D and E column. So the D and E column will have many rows that all had the A column value of 1. Then it will loop again and do the exact same thing for the next number which is 2. Only these will go in the F and G column. Let me know if I'm right on and I'll code it.
    yep, that's how I understood it.

  7. #7
    New Member
    Join Date
    Sep 2014
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: [VBA] Loop through a range in column A, copy data on the right according to a specific value in the looping range

    Yes I meant exactly that. Thanks.
    Regarding sorting, does it matter with ascending order (I constructed my worksheet with ascending order).
    Last edited by raulmadrid; Sep 26th, 2014 at 02:52 AM.

  8. #8
    New Member
    Join Date
    Sep 2014
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: [VBA] Loop through a range in column A, copy data on the right according to a specific value in the looping range

    Quote Originally Posted by WarPiglet View Post
    I think i understand. Is this what he ment? The loop will check every cell in the A column to see if the value is "1". If it is 1, then it will put the B and C values in the D and E column. So the D and E column will have many rows that all had the A column value of 1. Then it will loop again and do the exact same thing for the next number which is 2. Only these will go in the F and G column. Let me know if I'm right on and I'll code it.
    I meant exactly that. The numbers are not necessarily in ascending order (but I constructed my sheet in ascending order) and they can be any discrete value not necessarily continuous value. Just need to copy data related to unique value in column A.

  9. #9
    New Member
    Join Date
    Sep 2014
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: [VBA] Loop through a range in column A, copy data on the right according to a specific value in the looping range

    Quote Originally Posted by WarPiglet View Post
    I think i understand. Is this what he ment? The loop will check every cell in the A column to see if the value is "1". If it is 1, then it will put the B and C values in the D and E column. So the D and E column will have many rows that all had the A column value of 1. Then it will loop again and do the exact same thing for the next number which is 2. Only these will go in the F and G column. Let me know if I'm right on and I'll code it.
    Just an idea popping up in my mind, and I think it would be quicker: can we filter by each unique value in column A then copy visible cells only in B & C?

  10. #10
    Board Regular
    Join Date
    May 2014
    Posts
    1,592
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: [VBA] Loop through a range in column A, copy data on the right according to a specific value in the looping range

    Sorry I could not do it. I just tried. I came across a problem in my logic. I was trying to make the output column dependent on the value in the cell. The problem is that you have to outputs, so i can't do that. I also can't figure out a way to do it because the only other way I was trying to do it requires headers. So the headers for D1 and E1 would be 1, the headers for F1 and G1 would be 2. I would first create a loop to populate the headers dependent of unique numbers in the A column. Then I could create 2 nested Loops that first search for the header and populate the data in that column where the headers match the value of the A column. This loop would be in another loop that tells it to keep doing this until it finishes evaluating all the rows in the dataset. Without headers, I can't think of the logic that is needed to accomplish this task.

Some videos you may like

User Tag List

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
  •