Copying an entry from a list into a table in Excel VBA
2
I have a large list containing Personnel Names, Certification Names, and Certification Expiry Dates.
I am trying to write a script to copy the Certification Expiry Date for each entry into a table that has Personnel Names on one axis and Certification Names on the other axis.
The script needs to identify which cell in the table is the correct cell for each entry based on the Certification Name and Personnel Name, then copy the Certification Expiry Date into that cell.
I have written out step by step what needs to happen but am new to VBA so struggling to make it work.
excel vba excel-vba
asked Nov 18 '18 at 23:36
user2650243user2650243
28114
add a comment |
2
I have a large list containing Personnel Names, Certification Names, and Certification Expiry Dates.
I am trying to write a script to copy the Certification Expiry Date for each entry into a table that has Personnel Names on one axis and Certification Names on the other axis.
The script needs to identify which cell in the table is the correct cell for each entry based on the Certification Name and Personnel Name, then copy the Certification Expiry Date into that cell.
I have written out step by step what needs to happen but am new to VBA so struggling to make it work.
excel vba excel-vba
asked Nov 18 '18 at 23:36
user2650243user2650243
28114
Could you add sample data?
– JohnyL
Nov 19 '18 at 7:36
add a comment |
2
2
2
I have a large list containing Personnel Names, Certification Names, and Certification Expiry Dates.
I am trying to write a script to copy the Certification Expiry Date for each entry into a table that has Personnel Names on one axis and Certification Names on the other axis.
The script needs to identify which cell in the table is the correct cell for each entry based on the Certification Name and Personnel Name, then copy the Certification Expiry Date into that cell.
I have written out step by step what needs to happen but am new to VBA so struggling to make it work.
excel vba excel-vba
asked Nov 18 '18 at 23:36
user2650243user2650243
28114
I have a large list containing Personnel Names, Certification Names, and Certification Expiry Dates.
I am trying to write a script to copy the Certification Expiry Date for each entry into a table that has Personnel Names on one axis and Certification Names on the other axis.
The script needs to identify which cell in the table is the correct cell for each entry based on the Certification Name and Personnel Name, then copy the Certification Expiry Date into that cell.
I have written out step by step what needs to happen but am new to VBA so struggling to make it work.
excel vba excel-vba
excel vba excel-vba
asked Nov 18 '18 at 23:36
user2650243user2650243
28114
asked Nov 18 '18 at 23:36
user2650243user2650243
28114
asked Nov 18 '18 at 23:36
user2650243user2650243
28114
asked Nov 18 '18 at 23:36
user2650243user2650243
28114
asked Nov 18 '18 at 23:36
user2650243user2650243
28114
28114
Could you add sample data?
– JohnyL
Nov 19 '18 at 7:36
add a comment |
Could you add sample data?
– JohnyL
Nov 19 '18 at 7:36
Could you add sample data?
– JohnyL
Nov 19 '18 at 7:36
Could you add sample data?
– JohnyL
Nov 19 '18 at 7:36
add a comment |
1 Answer
1
active
oldest
votes
1
You don't need a macro to do it. Just use a pivot table:
If you really need VBA code (not very elegant, will submit it to CodeReview for improvement suggestions):
Sub PivotData()
Dim rng As Range, cll As Range
Dim arr As New Collection, a
Dim var() As Variant
Dim l As Long
Dim lRow As Long, lCol As Long
l = 1
Set rng = Range("A2:C7")
' Create unique list of names
var = Range("A2:A7")
On Error Resume Next
For Each a In var
arr.Add a, a
Next
For l = 1 To arr.Count
Cells(l + 1, 5) = arr(l)
Next
Set arr = Nothing
' Create unique list of certificates
var = Range("B2:B7")
For Each a In var
arr.Add a, a
Next
For l = 1 To arr.Count
Cells(1, 5 + l) = arr(l)
Next
Set arr = Nothing
On Error GoTo 0
Range("F2").FormulaArray = _
"=IFERROR(INDEX(R2C3:R7C3,MATCH(1,((R2C1:R7C1=RC5)*(R2C2:R7C2=R1C)),0)),"""")"
With Range("F2")
lRow = .CurrentRegion.Rows.Count
lCol = .CurrentRegion.Columns.Count + 4
End With
Range("F2:F" & lRow).FillDown
Range(Cells(2, 6), Cells(lRow, lCol)).FillRight
End Sub
answered Nov 18 '18 at 23:54
Michal RosaMichal Rosa
1,3161814
How can I use your VBA example if my source data has multiple other columns? For example it has columns A:M, but the data I want to use is in columns B, G, and L. Also I was hoping to have the data entered into a fixed table, rather than have excel generate the row and column labels like it does with a pivot table, how can I do this?
– user2650243
Nov 19 '18 at 2:01
Change the input references AA to BB, BB to GG etc. Also chage the references of the output table.You can have either a pivot table or cells populated by a macro. Not quite sure how else I could help you. Cheers,
– Michal Rosa
Nov 19 '18 at 2:14
Instead of usingCollectionfor getting rid of duplicates, it's better to useDictionary(I see, you use it everywhere where you eliminate duplicates).
– JohnyL
Nov 19 '18 at 7:35
Moreover, your style of addingOn Error Resume Nextdoesn't allow you to catch other errors.
– JohnyL
Nov 19 '18 at 7:37
Thank you for your valuable feedback. I am looking forward to seeing your code I can learn from. Regards,
– Michal Rosa
Nov 19 '18 at 7:48
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
});
}
});
draft saved
draft discarded
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%2f53366529%2fcopying-an-entry-from-a-list-into-a-table-in-excel-vba%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
1
You don't need a macro to do it. Just use a pivot table:
If you really need VBA code (not very elegant, will submit it to CodeReview for improvement suggestions):
Sub PivotData()
Dim rng As Range, cll As Range
Dim arr As New Collection, a
Dim var() As Variant
Dim l As Long
Dim lRow As Long, lCol As Long
l = 1
Set rng = Range("A2:C7")
' Create unique list of names
var = Range("A2:A7")
On Error Resume Next
For Each a In var
arr.Add a, a
Next
For l = 1 To arr.Count
Cells(l + 1, 5) = arr(l)
Next
Set arr = Nothing
' Create unique list of certificates
var = Range("B2:B7")
For Each a In var
arr.Add a, a
Next
For l = 1 To arr.Count
Cells(1, 5 + l) = arr(l)
Next
Set arr = Nothing
On Error GoTo 0
Range("F2").FormulaArray = _
"=IFERROR(INDEX(R2C3:R7C3,MATCH(1,((R2C1:R7C1=RC5)*(R2C2:R7C2=R1C)),0)),"""")"
With Range("F2")
lRow = .CurrentRegion.Rows.Count
lCol = .CurrentRegion.Columns.Count + 4
End With
Range("F2:F" & lRow).FillDown
Range(Cells(2, 6), Cells(lRow, lCol)).FillRight
End Sub
answered Nov 18 '18 at 23:54
Michal RosaMichal Rosa
1,3161814
How can I use your VBA example if my source data has multiple other columns? For example it has columns A:M, but the data I want to use is in columns B, G, and L. Also I was hoping to have the data entered into a fixed table, rather than have excel generate the row and column labels like it does with a pivot table, how can I do this?
– user2650243
Nov 19 '18 at 2:01
Change the input references AA to BB, BB to GG etc. Also chage the references of the output table.You can have either a pivot table or cells populated by a macro. Not quite sure how else I could help you. Cheers,
– Michal Rosa
Nov 19 '18 at 2:14
Instead of usingCollectionfor getting rid of duplicates, it's better to useDictionary(I see, you use it everywhere where you eliminate duplicates).
– JohnyL
Nov 19 '18 at 7:35
Moreover, your style of addingOn Error Resume Nextdoesn't allow you to catch other errors.
– JohnyL
Nov 19 '18 at 7:37
Thank you for your valuable feedback. I am looking forward to seeing your code I can learn from. Regards,
– Michal Rosa
Nov 19 '18 at 7:48
add a comment |
1
You don't need a macro to do it. Just use a pivot table:
If you really need VBA code (not very elegant, will submit it to CodeReview for improvement suggestions):
Sub PivotData()
Dim rng As Range, cll As Range
Dim arr As New Collection, a
Dim var() As Variant
Dim l As Long
Dim lRow As Long, lCol As Long
l = 1
Set rng = Range("A2:C7")
' Create unique list of names
var = Range("A2:A7")
On Error Resume Next
For Each a In var
arr.Add a, a
Next
For l = 1 To arr.Count
Cells(l + 1, 5) = arr(l)
Next
Set arr = Nothing
' Create unique list of certificates
var = Range("B2:B7")
For Each a In var
arr.Add a, a
Next
For l = 1 To arr.Count
Cells(1, 5 + l) = arr(l)
Next
Set arr = Nothing
On Error GoTo 0
Range("F2").FormulaArray = _
"=IFERROR(INDEX(R2C3:R7C3,MATCH(1,((R2C1:R7C1=RC5)*(R2C2:R7C2=R1C)),0)),"""")"
With Range("F2")
lRow = .CurrentRegion.Rows.Count
lCol = .CurrentRegion.Columns.Count + 4
End With
Range("F2:F" & lRow).FillDown
Range(Cells(2, 6), Cells(lRow, lCol)).FillRight
End Sub
answered Nov 18 '18 at 23:54
Michal RosaMichal Rosa
1,3161814
How can I use your VBA example if my source data has multiple other columns? For example it has columns A:M, but the data I want to use is in columns B, G, and L. Also I was hoping to have the data entered into a fixed table, rather than have excel generate the row and column labels like it does with a pivot table, how can I do this?
– user2650243
Nov 19 '18 at 2:01
Change the input references AA to BB, BB to GG etc. Also chage the references of the output table.You can have either a pivot table or cells populated by a macro. Not quite sure how else I could help you. Cheers,
– Michal Rosa
Nov 19 '18 at 2:14
Instead of usingCollectionfor getting rid of duplicates, it's better to useDictionary(I see, you use it everywhere where you eliminate duplicates).
– JohnyL
Nov 19 '18 at 7:35
Moreover, your style of addingOn Error Resume Nextdoesn't allow you to catch other errors.
– JohnyL
Nov 19 '18 at 7:37
Thank you for your valuable feedback. I am looking forward to seeing your code I can learn from. Regards,
– Michal Rosa
Nov 19 '18 at 7:48
add a comment |
1
1
1
You don't need a macro to do it. Just use a pivot table:
If you really need VBA code (not very elegant, will submit it to CodeReview for improvement suggestions):
Sub PivotData()
Dim rng As Range, cll As Range
Dim arr As New Collection, a
Dim var() As Variant
Dim l As Long
Dim lRow As Long, lCol As Long
l = 1
Set rng = Range("A2:C7")
' Create unique list of names
var = Range("A2:A7")
On Error Resume Next
For Each a In var
arr.Add a, a
Next
For l = 1 To arr.Count
Cells(l + 1, 5) = arr(l)
Next
Set arr = Nothing
' Create unique list of certificates
var = Range("B2:B7")
For Each a In var
arr.Add a, a
Next
For l = 1 To arr.Count
Cells(1, 5 + l) = arr(l)
Next
Set arr = Nothing
On Error GoTo 0
Range("F2").FormulaArray = _
"=IFERROR(INDEX(R2C3:R7C3,MATCH(1,((R2C1:R7C1=RC5)*(R2C2:R7C2=R1C)),0)),"""")"
With Range("F2")
lRow = .CurrentRegion.Rows.Count
lCol = .CurrentRegion.Columns.Count + 4
End With
Range("F2:F" & lRow).FillDown
Range(Cells(2, 6), Cells(lRow, lCol)).FillRight
End Sub
answered Nov 18 '18 at 23:54
Michal RosaMichal Rosa
1,3161814
You don't need a macro to do it. Just use a pivot table:
If you really need VBA code (not very elegant, will submit it to CodeReview for improvement suggestions):
Sub PivotData()
Dim rng As Range, cll As Range
Dim arr As New Collection, a
Dim var() As Variant
Dim l As Long
Dim lRow As Long, lCol As Long
l = 1
Set rng = Range("A2:C7")
' Create unique list of names
var = Range("A2:A7")
On Error Resume Next
For Each a In var
arr.Add a, a
Next
For l = 1 To arr.Count
Cells(l + 1, 5) = arr(l)
Next
Set arr = Nothing
' Create unique list of certificates
var = Range("B2:B7")
For Each a In var
arr.Add a, a
Next
For l = 1 To arr.Count
Cells(1, 5 + l) = arr(l)
Next
Set arr = Nothing
On Error GoTo 0
Range("F2").FormulaArray = _
"=IFERROR(INDEX(R2C3:R7C3,MATCH(1,((R2C1:R7C1=RC5)*(R2C2:R7C2=R1C)),0)),"""")"
With Range("F2")
lRow = .CurrentRegion.Rows.Count
lCol = .CurrentRegion.Columns.Count + 4
End With
Range("F2:F" & lRow).FillDown
Range(Cells(2, 6), Cells(lRow, lCol)).FillRight
End Sub
answered Nov 18 '18 at 23:54
Michal RosaMichal Rosa
1,3161814
edited Nov 19 '18 at 0:58
answered Nov 18 '18 at 23:54
Michal RosaMichal Rosa
1,3161814
answered Nov 18 '18 at 23:54
Michal RosaMichal Rosa
1,3161814
answered Nov 18 '18 at 23:54
Michal RosaMichal Rosa
1,3161814
1,3161814
How can I use your VBA example if my source data has multiple other columns? For example it has columns A:M, but the data I want to use is in columns B, G, and L. Also I was hoping to have the data entered into a fixed table, rather than have excel generate the row and column labels like it does with a pivot table, how can I do this?
– user2650243
Nov 19 '18 at 2:01
Change the input references AA to BB, BB to GG etc. Also chage the references of the output table.You can have either a pivot table or cells populated by a macro. Not quite sure how else I could help you. Cheers,
– Michal Rosa
Nov 19 '18 at 2:14
Instead of usingCollectionfor getting rid of duplicates, it's better to useDictionary(I see, you use it everywhere where you eliminate duplicates).
– JohnyL
Nov 19 '18 at 7:35
Moreover, your style of addingOn Error Resume Nextdoesn't allow you to catch other errors.
– JohnyL
Nov 19 '18 at 7:37
Thank you for your valuable feedback. I am looking forward to seeing your code I can learn from. Regards,
– Michal Rosa
Nov 19 '18 at 7:48
add a comment |
How can I use your VBA example if my source data has multiple other columns? For example it has columns A:M, but the data I want to use is in columns B, G, and L. Also I was hoping to have the data entered into a fixed table, rather than have excel generate the row and column labels like it does with a pivot table, how can I do this?
– user2650243
Nov 19 '18 at 2:01
Change the input references AA to BB, BB to GG etc. Also chage the references of the output table.You can have either a pivot table or cells populated by a macro. Not quite sure how else I could help you. Cheers,
– Michal Rosa
Nov 19 '18 at 2:14
Instead of usingCollectionfor getting rid of duplicates, it's better to useDictionary(I see, you use it everywhere where you eliminate duplicates).
– JohnyL
Nov 19 '18 at 7:35
Moreover, your style of addingOn Error Resume Nextdoesn't allow you to catch other errors.
– JohnyL
Nov 19 '18 at 7:37
Thank you for your valuable feedback. I am looking forward to seeing your code I can learn from. Regards,
– Michal Rosa
Nov 19 '18 at 7:48
How can I use your VBA example if my source data has multiple other columns? For example it has columns A:M, but the data I want to use is in columns B, G, and L. Also I was hoping to have the data entered into a fixed table, rather than have excel generate the row and column labels like it does with a pivot table, how can I do this?
– user2650243
Nov 19 '18 at 2:01
How can I use your VBA example if my source data has multiple other columns? For example it has columns A:M, but the data I want to use is in columns B, G, and L. Also I was hoping to have the data entered into a fixed table, rather than have excel generate the row and column labels like it does with a pivot table, how can I do this?
– user2650243
Nov 19 '18 at 2:01
Change the input references AA to BB, BB to GG etc. Also chage the references of the output table.You can have either a pivot table or cells populated by a macro. Not quite sure how else I could help you. Cheers,
– Michal Rosa
Nov 19 '18 at 2:14
Change the input references AA to BB, BB to GG etc. Also chage the references of the output table.You can have either a pivot table or cells populated by a macro. Not quite sure how else I could help you. Cheers,
– Michal Rosa
Nov 19 '18 at 2:14
Instead of using
Collection for getting rid of duplicates, it's better to use Dictionary (I see, you use it everywhere where you eliminate duplicates).– JohnyL
Nov 19 '18 at 7:35
Instead of using
Collection for getting rid of duplicates, it's better to use Dictionary (I see, you use it everywhere where you eliminate duplicates).– JohnyL
Nov 19 '18 at 7:35
Moreover, your style of adding
On Error Resume Next doesn't allow you to catch other errors.– JohnyL
Nov 19 '18 at 7:37
Moreover, your style of adding
On Error Resume Next doesn't allow you to catch other errors.– JohnyL
Nov 19 '18 at 7:37
Thank you for your valuable feedback. I am looking forward to seeing your code I can learn from. Regards,
– Michal Rosa
Nov 19 '18 at 7:48
Thank you for your valuable feedback. I am looking forward to seeing your code I can learn from. Regards,
– Michal Rosa
Nov 19 '18 at 7:48
add a comment |
draft saved
draft discarded
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.
draft saved
draft discarded
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%2f53366529%2fcopying-an-entry-from-a-list-into-a-table-in-excel-vba%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
Could you add sample data?
– JohnyL
Nov 19 '18 at 7:36