Excel VBA - Find all rows with a specific value and get their row number
I have close to zero knowledge in excel and vba.
What I'm trying to do the the following:
for each row in ActiveSheet.ListObjects("SheetPotatoData")
if cell (column 5):(row) value equals "potato"
do something with (column 2):(row)
I would really appreciate it if you could enlighten me on the proper syntax to do this.
Thank you very much!
excel vba excel-vba ms-office
asked Aug 24 '16 at 11:34
SinrosSinros
8428
add a comment |
I have close to zero knowledge in excel and vba.
What I'm trying to do the the following:
for each row in ActiveSheet.ListObjects("SheetPotatoData")
if cell (column 5):(row) value equals "potato"
do something with (column 2):(row)
I would really appreciate it if you could enlighten me on the proper syntax to do this.
Thank you very much!
excel vba excel-vba ms-office
asked Aug 24 '16 at 11:34
SinrosSinros
8428
google.co.uk/…
– Nathan_Sav
Aug 24 '16 at 11:37
add a comment |
I have close to zero knowledge in excel and vba.
What I'm trying to do the the following:
for each row in ActiveSheet.ListObjects("SheetPotatoData")
if cell (column 5):(row) value equals "potato"
do something with (column 2):(row)
I would really appreciate it if you could enlighten me on the proper syntax to do this.
Thank you very much!
excel vba excel-vba ms-office
asked Aug 24 '16 at 11:34
SinrosSinros
8428
I have close to zero knowledge in excel and vba.
What I'm trying to do the the following:
for each row in ActiveSheet.ListObjects("SheetPotatoData")
if cell (column 5):(row) value equals "potato"
do something with (column 2):(row)
I would really appreciate it if you could enlighten me on the proper syntax to do this.
Thank you very much!
excel vba excel-vba ms-office
excel vba excel-vba ms-office
asked Aug 24 '16 at 11:34
SinrosSinros
8428
asked Aug 24 '16 at 11:34
SinrosSinros
8428
asked Aug 24 '16 at 11:34
SinrosSinros
8428
asked Aug 24 '16 at 11:34
SinrosSinros
8428
asked Aug 24 '16 at 11:34
SinrosSinros
8428
8428
google.co.uk/…
– Nathan_Sav
Aug 24 '16 at 11:37
add a comment |
google.co.uk/…
– Nathan_Sav
Aug 24 '16 at 11:37
google.co.uk/…
– Nathan_Sav
Aug 24 '16 at 11:37
google.co.uk/…
– Nathan_Sav
Aug 24 '16 at 11:37
add a comment |
2 Answers
2
active
oldest
votes
Look here: http://www.excel-easy.com/vba/range-object.html
For i = 1 To ActiveSheet.ListObjects("TableNameHere").ListRows.Count
If Cells(i,5).Value = "potato" Then
Cells(i,2).Value = "New value"
End If
Next
answered Aug 24 '16 at 11:39
Daniel LeeDaniel Lee
2,52711325
Thank you for not giving me a link to a google search :) This is exactly what I was looking for! I simply replaced the 50 with ActiveSheet.ListObjects("TableNameHere").ListRows.Count and it loops on the whole table now.
– Sinros
Aug 24 '16 at 11:54
Updated answer withActiveSheet.ListObjects("TableNameHere").ListRows.Count
– Daniel Lee
Aug 24 '16 at 12:43
add a comment |
Alternatively, just add ".DataBodyRange.Rows" to the end of the first line in your line "For... Each" structure. You're asking VBA to iterate through each Row in a ListObject, rather each Row in the collection of Rows contained within the ListObject.
That allows sticking with the more elegant, readable, and reliable 'For...Each' structure rather than fetching range bounds and traversing it using counters. Using the DataBodyRange property of the list allows you to automatically handle any Header rows that may be present and not include them in your row inspection loop. You can reference the ListObject.Rows collection instead if you want Headers included.
Final code would look something like:
For Each row In ActiveSheet.ListObjects("SheetPotatoData").DataBodyRows.Rows
if row.Cells(1,5) = "potato"
'Do something
End If
Next
answered Nov 13 '18 at 22:04
PairrieNerd403PairrieNerd403
1
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%2f39122217%2fexcel-vba-find-all-rows-with-a-specific-value-and-get-their-row-number%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
Look here: http://www.excel-easy.com/vba/range-object.html
For i = 1 To ActiveSheet.ListObjects("TableNameHere").ListRows.Count
If Cells(i,5).Value = "potato" Then
Cells(i,2).Value = "New value"
End If
Next
answered Aug 24 '16 at 11:39
Daniel LeeDaniel Lee
2,52711325
Thank you for not giving me a link to a google search :) This is exactly what I was looking for! I simply replaced the 50 with ActiveSheet.ListObjects("TableNameHere").ListRows.Count and it loops on the whole table now.
– Sinros
Aug 24 '16 at 11:54
Updated answer withActiveSheet.ListObjects("TableNameHere").ListRows.Count
– Daniel Lee
Aug 24 '16 at 12:43
add a comment |
Look here: http://www.excel-easy.com/vba/range-object.html
For i = 1 To ActiveSheet.ListObjects("TableNameHere").ListRows.Count
If Cells(i,5).Value = "potato" Then
Cells(i,2).Value = "New value"
End If
Next
answered Aug 24 '16 at 11:39
Daniel LeeDaniel Lee
2,52711325
Thank you for not giving me a link to a google search :) This is exactly what I was looking for! I simply replaced the 50 with ActiveSheet.ListObjects("TableNameHere").ListRows.Count and it loops on the whole table now.
– Sinros
Aug 24 '16 at 11:54
Updated answer withActiveSheet.ListObjects("TableNameHere").ListRows.Count
– Daniel Lee
Aug 24 '16 at 12:43
add a comment |
Look here: http://www.excel-easy.com/vba/range-object.html
For i = 1 To ActiveSheet.ListObjects("TableNameHere").ListRows.Count
If Cells(i,5).Value = "potato" Then
Cells(i,2).Value = "New value"
End If
Next
answered Aug 24 '16 at 11:39
Daniel LeeDaniel Lee
2,52711325
Look here: http://www.excel-easy.com/vba/range-object.html
For i = 1 To ActiveSheet.ListObjects("TableNameHere").ListRows.Count
If Cells(i,5).Value = "potato" Then
Cells(i,2).Value = "New value"
End If
Next
answered Aug 24 '16 at 11:39
Daniel LeeDaniel Lee
2,52711325
edited Aug 24 '16 at 12:43
answered Aug 24 '16 at 11:39
Daniel LeeDaniel Lee
2,52711325
answered Aug 24 '16 at 11:39
Daniel LeeDaniel Lee
2,52711325
answered Aug 24 '16 at 11:39
Daniel LeeDaniel Lee
2,52711325
2,52711325
Thank you for not giving me a link to a google search :) This is exactly what I was looking for! I simply replaced the 50 with ActiveSheet.ListObjects("TableNameHere").ListRows.Count and it loops on the whole table now.
– Sinros
Aug 24 '16 at 11:54
Updated answer withActiveSheet.ListObjects("TableNameHere").ListRows.Count
– Daniel Lee
Aug 24 '16 at 12:43
add a comment |
Thank you for not giving me a link to a google search :) This is exactly what I was looking for! I simply replaced the 50 with ActiveSheet.ListObjects("TableNameHere").ListRows.Count and it loops on the whole table now.
– Sinros
Aug 24 '16 at 11:54
Updated answer withActiveSheet.ListObjects("TableNameHere").ListRows.Count
– Daniel Lee
Aug 24 '16 at 12:43
Thank you for not giving me a link to a google search :) This is exactly what I was looking for! I simply replaced the 50 with ActiveSheet.ListObjects("TableNameHere").ListRows.Count and it loops on the whole table now.
– Sinros
Aug 24 '16 at 11:54
Thank you for not giving me a link to a google search :) This is exactly what I was looking for! I simply replaced the 50 with ActiveSheet.ListObjects("TableNameHere").ListRows.Count and it loops on the whole table now.
– Sinros
Aug 24 '16 at 11:54
Updated answer with
ActiveSheet.ListObjects("TableNameHere").ListRows.Count– Daniel Lee
Aug 24 '16 at 12:43
Updated answer with
ActiveSheet.ListObjects("TableNameHere").ListRows.Count– Daniel Lee
Aug 24 '16 at 12:43
add a comment |
Alternatively, just add ".DataBodyRange.Rows" to the end of the first line in your line "For... Each" structure. You're asking VBA to iterate through each Row in a ListObject, rather each Row in the collection of Rows contained within the ListObject.
That allows sticking with the more elegant, readable, and reliable 'For...Each' structure rather than fetching range bounds and traversing it using counters. Using the DataBodyRange property of the list allows you to automatically handle any Header rows that may be present and not include them in your row inspection loop. You can reference the ListObject.Rows collection instead if you want Headers included.
Final code would look something like:
For Each row In ActiveSheet.ListObjects("SheetPotatoData").DataBodyRows.Rows
if row.Cells(1,5) = "potato"
'Do something
End If
Next
answered Nov 13 '18 at 22:04
PairrieNerd403PairrieNerd403
1
add a comment |
Alternatively, just add ".DataBodyRange.Rows" to the end of the first line in your line "For... Each" structure. You're asking VBA to iterate through each Row in a ListObject, rather each Row in the collection of Rows contained within the ListObject.
That allows sticking with the more elegant, readable, and reliable 'For...Each' structure rather than fetching range bounds and traversing it using counters. Using the DataBodyRange property of the list allows you to automatically handle any Header rows that may be present and not include them in your row inspection loop. You can reference the ListObject.Rows collection instead if you want Headers included.
Final code would look something like:
For Each row In ActiveSheet.ListObjects("SheetPotatoData").DataBodyRows.Rows
if row.Cells(1,5) = "potato"
'Do something
End If
Next
answered Nov 13 '18 at 22:04
PairrieNerd403PairrieNerd403
1
add a comment |
Alternatively, just add ".DataBodyRange.Rows" to the end of the first line in your line "For... Each" structure. You're asking VBA to iterate through each Row in a ListObject, rather each Row in the collection of Rows contained within the ListObject.
That allows sticking with the more elegant, readable, and reliable 'For...Each' structure rather than fetching range bounds and traversing it using counters. Using the DataBodyRange property of the list allows you to automatically handle any Header rows that may be present and not include them in your row inspection loop. You can reference the ListObject.Rows collection instead if you want Headers included.
Final code would look something like:
For Each row In ActiveSheet.ListObjects("SheetPotatoData").DataBodyRows.Rows
if row.Cells(1,5) = "potato"
'Do something
End If
Next
answered Nov 13 '18 at 22:04
PairrieNerd403PairrieNerd403
1
Alternatively, just add ".DataBodyRange.Rows" to the end of the first line in your line "For... Each" structure. You're asking VBA to iterate through each Row in a ListObject, rather each Row in the collection of Rows contained within the ListObject.
That allows sticking with the more elegant, readable, and reliable 'For...Each' structure rather than fetching range bounds and traversing it using counters. Using the DataBodyRange property of the list allows you to automatically handle any Header rows that may be present and not include them in your row inspection loop. You can reference the ListObject.Rows collection instead if you want Headers included.
Final code would look something like:
For Each row In ActiveSheet.ListObjects("SheetPotatoData").DataBodyRows.Rows
if row.Cells(1,5) = "potato"
'Do something
End If
Next
answered Nov 13 '18 at 22:04
PairrieNerd403PairrieNerd403
1
answered Nov 13 '18 at 22:04
PairrieNerd403PairrieNerd403
1
answered Nov 13 '18 at 22:04
PairrieNerd403PairrieNerd403
1
answered Nov 13 '18 at 22:04
PairrieNerd403PairrieNerd403
1
1
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%2f39122217%2fexcel-vba-find-all-rows-with-a-specific-value-and-get-their-row-number%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
google.co.uk/…
– Nathan_Sav
Aug 24 '16 at 11:37