For Loop Skipping
0
I'm trying to create a for loop that looks at data in column G and depending on its value will cut and paste it to another sheet. I've been able to get the copy and paste working but when I run the macro it will cut and paste row 3 when n = 2 which leads to rows being skipped. Stepping through the code n is the correct value but it seems to cut n+1. J,K, and L are used in additional IF statements that I've removed for the time being.
Thanks,
Dim n As Integer: n = 1
Dim i As Long: i = 1
Dim j As Long: i = 1
Dim k As Long: i = 1
Dim l As Long: i = 1
Dim newSheetName As String
Dim checkSheetName As String
ActiveSheet.UsedRange
Set r = ActiveSheet.UsedRange
nLastRow = r.Rows.Count + r.Row - 1
nFirstRow = r.Row
With ActiveSheet.UsedRange
For n = 1 To 20
If Cells(n, "D") <> "" Then
If Cells(n, "G") Like "*1492-J4*" Or Cells(n, "G") Like "*1492-J6*" Or Cells(n, "G") Like "*1492-J10*" Or Cells(n, "G") Like "*1492-JD4*" Or Cells(n, "G") Like "*1492-H*" Then
.Cells(n, "D").EntireRow.Cut Destination:=Worksheets("UCT-TM6").Range("A" & i)
'.Cells(n, "D").EntireRow.Delete
i = i + 1
Else
MsgBox "The part number " & Range("G" & n).Text & " does not exisit in database"
End If
End If
Next
End With
excel vba for-loop
asked Nov 20 '18 at 19:06
NelsonaNelsona
11
add a comment |
0
I'm trying to create a for loop that looks at data in column G and depending on its value will cut and paste it to another sheet. I've been able to get the copy and paste working but when I run the macro it will cut and paste row 3 when n = 2 which leads to rows being skipped. Stepping through the code n is the correct value but it seems to cut n+1. J,K, and L are used in additional IF statements that I've removed for the time being.
Thanks,
Dim n As Integer: n = 1
Dim i As Long: i = 1
Dim j As Long: i = 1
Dim k As Long: i = 1
Dim l As Long: i = 1
Dim newSheetName As String
Dim checkSheetName As String
ActiveSheet.UsedRange
Set r = ActiveSheet.UsedRange
nLastRow = r.Rows.Count + r.Row - 1
nFirstRow = r.Row
With ActiveSheet.UsedRange
For n = 1 To 20
If Cells(n, "D") <> "" Then
If Cells(n, "G") Like "*1492-J4*" Or Cells(n, "G") Like "*1492-J6*" Or Cells(n, "G") Like "*1492-J10*" Or Cells(n, "G") Like "*1492-JD4*" Or Cells(n, "G") Like "*1492-H*" Then
.Cells(n, "D").EntireRow.Cut Destination:=Worksheets("UCT-TM6").Range("A" & i)
'.Cells(n, "D").EntireRow.Delete
i = i + 1
Else
MsgBox "The part number " & Range("G" & n).Text & " does not exisit in database"
End If
End If
Next
End With
excel vba for-loop
asked Nov 20 '18 at 19:06
NelsonaNelsona
11
5
Try looping backwards:For n = 20 To 1 step -1
– cybernetic.nomad
Nov 20 '18 at 19:46
1
To be a little clearer you are deleting rows in a loop. When you delete row 2, row 3 becomes row 2, so there are now only 19 rows in your range but you are still counting to 20. By using step -1 deleting rows doesn't affect the rows you still have to process e.g. deleting row 18 makes row 19 the new 18 but you don't care because you already processed row 19.
– Freeflow
Nov 20 '18 at 20:25
I've already commented out the delete portion of the code. I saw it was giving me that problem but I still wanted to address it at a later point. I tried the looping backwards it still skipped rows. Not real familiar with VBA yet, but by chance does (Cells(n, "D").EntireRow.Cut) cut the next row automatically instead of specified row?
– Nelsona
Nov 20 '18 at 20:48
Figured out the issue, changing (With ActiveSheet.UsedRange) to (With ActiveSheet) corrected it.
– Nelsona
Nov 20 '18 at 21:37
add a comment |
0
0
0
I'm trying to create a for loop that looks at data in column G and depending on its value will cut and paste it to another sheet. I've been able to get the copy and paste working but when I run the macro it will cut and paste row 3 when n = 2 which leads to rows being skipped. Stepping through the code n is the correct value but it seems to cut n+1. J,K, and L are used in additional IF statements that I've removed for the time being.
Thanks,
Dim n As Integer: n = 1
Dim i As Long: i = 1
Dim j As Long: i = 1
Dim k As Long: i = 1
Dim l As Long: i = 1
Dim newSheetName As String
Dim checkSheetName As String
ActiveSheet.UsedRange
Set r = ActiveSheet.UsedRange
nLastRow = r.Rows.Count + r.Row - 1
nFirstRow = r.Row
With ActiveSheet.UsedRange
For n = 1 To 20
If Cells(n, "D") <> "" Then
If Cells(n, "G") Like "*1492-J4*" Or Cells(n, "G") Like "*1492-J6*" Or Cells(n, "G") Like "*1492-J10*" Or Cells(n, "G") Like "*1492-JD4*" Or Cells(n, "G") Like "*1492-H*" Then
.Cells(n, "D").EntireRow.Cut Destination:=Worksheets("UCT-TM6").Range("A" & i)
'.Cells(n, "D").EntireRow.Delete
i = i + 1
Else
MsgBox "The part number " & Range("G" & n).Text & " does not exisit in database"
End If
End If
Next
End With
excel vba for-loop
asked Nov 20 '18 at 19:06
NelsonaNelsona
11
I'm trying to create a for loop that looks at data in column G and depending on its value will cut and paste it to another sheet. I've been able to get the copy and paste working but when I run the macro it will cut and paste row 3 when n = 2 which leads to rows being skipped. Stepping through the code n is the correct value but it seems to cut n+1. J,K, and L are used in additional IF statements that I've removed for the time being.
Thanks,
Dim n As Integer: n = 1
Dim i As Long: i = 1
Dim j As Long: i = 1
Dim k As Long: i = 1
Dim l As Long: i = 1
Dim newSheetName As String
Dim checkSheetName As String
ActiveSheet.UsedRange
Set r = ActiveSheet.UsedRange
nLastRow = r.Rows.Count + r.Row - 1
nFirstRow = r.Row
With ActiveSheet.UsedRange
For n = 1 To 20
If Cells(n, "D") <> "" Then
If Cells(n, "G") Like "*1492-J4*" Or Cells(n, "G") Like "*1492-J6*" Or Cells(n, "G") Like "*1492-J10*" Or Cells(n, "G") Like "*1492-JD4*" Or Cells(n, "G") Like "*1492-H*" Then
.Cells(n, "D").EntireRow.Cut Destination:=Worksheets("UCT-TM6").Range("A" & i)
'.Cells(n, "D").EntireRow.Delete
i = i + 1
Else
MsgBox "The part number " & Range("G" & n).Text & " does not exisit in database"
End If
End If
Next
End With
excel vba for-loop
excel vba for-loop
asked Nov 20 '18 at 19:06
NelsonaNelsona
11
asked Nov 20 '18 at 19:06
NelsonaNelsona
11
edited Nov 20 '18 at 19:41
Nelsona
asked Nov 20 '18 at 19:06
NelsonaNelsona
11
asked Nov 20 '18 at 19:06
NelsonaNelsona
11
asked Nov 20 '18 at 19:06
NelsonaNelsona
11
11
5
Try looping backwards:For n = 20 To 1 step -1
– cybernetic.nomad
Nov 20 '18 at 19:46
1
To be a little clearer you are deleting rows in a loop. When you delete row 2, row 3 becomes row 2, so there are now only 19 rows in your range but you are still counting to 20. By using step -1 deleting rows doesn't affect the rows you still have to process e.g. deleting row 18 makes row 19 the new 18 but you don't care because you already processed row 19.
– Freeflow
Nov 20 '18 at 20:25
I've already commented out the delete portion of the code. I saw it was giving me that problem but I still wanted to address it at a later point. I tried the looping backwards it still skipped rows. Not real familiar with VBA yet, but by chance does (Cells(n, "D").EntireRow.Cut) cut the next row automatically instead of specified row?
– Nelsona
Nov 20 '18 at 20:48
Figured out the issue, changing (With ActiveSheet.UsedRange) to (With ActiveSheet) corrected it.
– Nelsona
Nov 20 '18 at 21:37
add a comment |
5
Try looping backwards:For n = 20 To 1 step -1
– cybernetic.nomad
Nov 20 '18 at 19:46
1
To be a little clearer you are deleting rows in a loop. When you delete row 2, row 3 becomes row 2, so there are now only 19 rows in your range but you are still counting to 20. By using step -1 deleting rows doesn't affect the rows you still have to process e.g. deleting row 18 makes row 19 the new 18 but you don't care because you already processed row 19.
– Freeflow
Nov 20 '18 at 20:25
I've already commented out the delete portion of the code. I saw it was giving me that problem but I still wanted to address it at a later point. I tried the looping backwards it still skipped rows. Not real familiar with VBA yet, but by chance does (Cells(n, "D").EntireRow.Cut) cut the next row automatically instead of specified row?
– Nelsona
Nov 20 '18 at 20:48
Figured out the issue, changing (With ActiveSheet.UsedRange) to (With ActiveSheet) corrected it.
– Nelsona
Nov 20 '18 at 21:37
5
5
Try looping backwards:
For n = 20 To 1 step -1– cybernetic.nomad
Nov 20 '18 at 19:46
Try looping backwards:
For n = 20 To 1 step -1– cybernetic.nomad
Nov 20 '18 at 19:46
1
1
To be a little clearer you are deleting rows in a loop. When you delete row 2, row 3 becomes row 2, so there are now only 19 rows in your range but you are still counting to 20. By using step -1 deleting rows doesn't affect the rows you still have to process e.g. deleting row 18 makes row 19 the new 18 but you don't care because you already processed row 19.
– Freeflow
Nov 20 '18 at 20:25
To be a little clearer you are deleting rows in a loop. When you delete row 2, row 3 becomes row 2, so there are now only 19 rows in your range but you are still counting to 20. By using step -1 deleting rows doesn't affect the rows you still have to process e.g. deleting row 18 makes row 19 the new 18 but you don't care because you already processed row 19.
– Freeflow
Nov 20 '18 at 20:25
I've already commented out the delete portion of the code. I saw it was giving me that problem but I still wanted to address it at a later point. I tried the looping backwards it still skipped rows. Not real familiar with VBA yet, but by chance does (Cells(n, "D").EntireRow.Cut) cut the next row automatically instead of specified row?
– Nelsona
Nov 20 '18 at 20:48
I've already commented out the delete portion of the code. I saw it was giving me that problem but I still wanted to address it at a later point. I tried the looping backwards it still skipped rows. Not real familiar with VBA yet, but by chance does (Cells(n, "D").EntireRow.Cut) cut the next row automatically instead of specified row?
– Nelsona
Nov 20 '18 at 20:48
Figured out the issue, changing (With ActiveSheet.UsedRange) to (With ActiveSheet) corrected it.
– Nelsona
Nov 20 '18 at 21:37
Figured out the issue, changing (With ActiveSheet.UsedRange) to (With ActiveSheet) corrected it.
– Nelsona
Nov 20 '18 at 21:37
add a comment |
0
active
oldest
votes
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%2f53399880%2ffor-loop-skipping%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
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%2f53399880%2ffor-loop-skipping%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
5
Try looping backwards:
For n = 20 To 1 step -1– cybernetic.nomad
Nov 20 '18 at 19:46
1
To be a little clearer you are deleting rows in a loop. When you delete row 2, row 3 becomes row 2, so there are now only 19 rows in your range but you are still counting to 20. By using step -1 deleting rows doesn't affect the rows you still have to process e.g. deleting row 18 makes row 19 the new 18 but you don't care because you already processed row 19.
– Freeflow
Nov 20 '18 at 20:25
I've already commented out the delete portion of the code. I saw it was giving me that problem but I still wanted to address it at a later point. I tried the looping backwards it still skipped rows. Not real familiar with VBA yet, but by chance does (Cells(n, "D").EntireRow.Cut) cut the next row automatically instead of specified row?
– Nelsona
Nov 20 '18 at 20:48
Figured out the issue, changing (With ActiveSheet.UsedRange) to (With ActiveSheet) corrected it.
– Nelsona
Nov 20 '18 at 21:37