Excel VBA: Copy rows where column value is same
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?
excel vba
asked Nov 23 '18 at 20:02
user3845582user3845582
94110
add a comment |
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?
excel vba
asked Nov 23 '18 at 20:02
user3845582user3845582
94110
Unless you absolutely bneed VBA, you could use a VLOOKUP to return multiple results
– cybernetic.nomad
Nov 23 '18 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
Nov 23 '18 at 20:15
add a comment |
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?
excel vba
asked Nov 23 '18 at 20:02
user3845582user3845582
94110
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?
excel vba
excel vba
asked Nov 23 '18 at 20:02
user3845582user3845582
94110
asked Nov 23 '18 at 20:02
user3845582user3845582
94110
edited Nov 23 '18 at 20:16
user3845582
asked Nov 23 '18 at 20:02
user3845582user3845582
94110
asked Nov 23 '18 at 20:02
user3845582user3845582
94110
asked Nov 23 '18 at 20:02
user3845582user3845582
94110
94110
Unless you absolutely bneed VBA, you could use a VLOOKUP to return multiple results
– cybernetic.nomad
Nov 23 '18 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
Nov 23 '18 at 20:15
add a comment |
Unless you absolutely bneed VBA, you could use a VLOOKUP to return multiple results
– cybernetic.nomad
Nov 23 '18 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
Nov 23 '18 at 20:15
Unless you absolutely bneed VBA, you could use a VLOOKUP to return multiple results
– cybernetic.nomad
Nov 23 '18 at 20:08
Unless you absolutely bneed VBA, you could use a VLOOKUP to return multiple results
– cybernetic.nomad
Nov 23 '18 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
Nov 23 '18 at 20:15
@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
Nov 23 '18 at 20:15
add a comment |
1 Answer
1
active
oldest
votes
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 '18 at 20:23
Michal RosaMichal Rosa
1,3191814
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53452373%2fexcel-vba-copy-rows-where-column-value-is-same%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
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 '18 at 20:23
Michal RosaMichal Rosa
1,3191814
add a comment |
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 '18 at 20:23
Michal RosaMichal Rosa
1,3191814
add a comment |
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 '18 at 20:23
Michal RosaMichal Rosa
1,3191814
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 '18 at 20:23
Michal RosaMichal Rosa
1,3191814
answered Nov 23 '18 at 20:23
Michal RosaMichal Rosa
1,3191814
answered Nov 23 '18 at 20:23
Michal RosaMichal Rosa
1,3191814
answered Nov 23 '18 at 20:23
Michal RosaMichal Rosa
1,3191814
1,3191814
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53452373%2fexcel-vba-copy-rows-where-column-value-is-same%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Unless you absolutely bneed VBA, you could use a VLOOKUP to return multiple results
– cybernetic.nomad
Nov 23 '18 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
Nov 23 '18 at 20:15