Excel VBA: Copy rows where column value is same

Question

Aim: I want to insert the the value in [Col2] & [Numeric Column] as a new row into a template for all where value in [Col1] are same. I also want to use the value in [Col1] in one cell.

Some additional info: Column 1 is the identifier/counterpart for an invoice. Column 2 is the type of product and Numeric Column is the currency amount.

I have a table:

Col1 Col2 Col3 Numeric Column 0001 Value B Ref1 100 0001 Value B Ref2 101 0001 Value C Ref3 99 0002 Value C Ref4 100 0002 Value B Ref5 101 0003 Value C Ref6 99 0004 Value B Ref7 100 0004 Value C Ref8 101

What I am trying to achieve is :

Sub Example()
Dim n As Integer
Dim Source As Worksheet
Dim Target As Worksheet

Set Source = ActiveWorkbook.Worksheets("source")
Set Target = ActiveWorkbook.Worksheets("target")

For each n = 2 in Range([Col1]) 
  //Where the Values in Col1 are the same

  //Copy Value in Col 1 to Target Sheet in cell A1 {used only once}
  //Copy each value in Col1 - Col3 into row 2 and below for each value where Col 1 is same

How do I set a variable that will continue to do something until the same values in [Col 1] are exhausted, then change to the next set of values in [col 1] without having to reference the unique values of [col 1] in a separate table/sheet?

Unless you absolutely bneed VBA, you could use a VLOOKUP to return multiple results — cybernetic.nomad
– cybernetic.nomad, Commented Nov 23, 2018 at 20:08
@cybernetic.nomad I need to copy the values to a template /populate an invoice. The total number of invoices is in the hundreds so I need to use VBA. Thanks :) — user3845582
– user3845582, Commented Nov 23, 2018 at 20:15

Michal · Accepted Answer · 2018-11-23 20:23:13Z

1

What you need is a dictionary. You need something like:

 Dim dict As Scripting.Dictionary
 Set dict = New Scripting.Dictionary
 Dim i As Long
 With dict
     For i = 2 To .Cells(.Rows.Count, "A").End(xlUp).Row
         If Not dict.Exists(.Cells(i, 1).Value2) Then dict.Add .Cells(i, 1).Value2, i
     Next
 End With

answered Nov 23, 2018 at 20:23

Michal

7,6894 gold badges30 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Excel VBA: Copy rows where column value is same

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related