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

raulmadrid

New Member
Joined
Sep 25, 2014
Messages
8
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 AColumn BColumn C
1aaa
2bbb
2bbb
2bbb
2bbb
3ccc
3ccc

<tbody>
</tbody>

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:

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

WarPigl3t

Well-known Member
Joined
May 25, 2014
Messages
1,609
Not enough information provided. Please provide a dataset that has the desired results in it because your explination is not enough.
 

ztodd

Board Regular
Joined
Sep 17, 2014
Messages
221
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.
 

WarPigl3t

Well-known Member
Joined
May 25, 2014
Messages
1,609
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.
 

ztodd

Board Regular
Joined
Sep 17, 2014
Messages
221

ADVERTISEMENT

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
 

ztodd

Board Regular
Joined
Sep 17, 2014
Messages
221
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.
 

raulmadrid

New Member
Joined
Sep 25, 2014
Messages
8

ADVERTISEMENT

Yes I meant exactly that. Thanks.
Regarding sorting, does it matter with ascending order (I constructed my worksheet with ascending order).
 
Last edited:

raulmadrid

New Member
Joined
Sep 25, 2014
Messages
8
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.
 

raulmadrid

New Member
Joined
Sep 25, 2014
Messages
8
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?
 

WarPigl3t

Well-known Member
Joined
May 25, 2014
Messages
1,609
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.
 

Forum statistics

Threads
1,140,928
Messages
5,703,218
Members
421,283
Latest member
MacroBegin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top