Create a checkpoint in a foreach statementHow to create Excel (.XLS and .XLSX) file in C# without installing Ms Office?Find data cell value, move up one cell in column, find next data cell within a column and get next valueCreate Cells starting at date x and ending at date yExcel VBA: Having trouble understanding LoopsExcel VBA - Selection Routine'If … Then' statement with loopTranspose visible cells till last columnVBA IF statement Loop If values are equalExcel VBA macro to send emails to unique users in rangeHow can I add looping per 250 cells and offset the array?
Why doesn't a marching band have strings?
How to split an equation over two lines?
Intuitively, why does putting capacitors in series decrease the equivalent capacitance?
Fedora boot screen shows both Fedora logo and Lenovo logo. Why and How?
Is there any set of 2-6 notes that doesn't have a chord name?
What happens when your group is victim of a surprise attack but you can't be surprised?
Is adding a new player (or players) a DM decision, or a group decision?
Why is the Turkish president's surname spelt in Russian as Эрдоган, with г?
What reason would an alien civilization have for building a Dyson Sphere (or Swarm) if cheap Nuclear fusion is available?
How does a blind passenger not die, if driver becomes unconscious
Is there vegetarian astronaut?
When is it ok to add filler to a story?
Links to webpages in books
Why is the voltage measurement of this circuit different when the switch is on?
Was there ever a name for the weapons of the Others?
Should I hide continue button until tasks are completed?
What happens when I sacrifice a creature when my Teysa Karlov is on the battlefield?
Is this one of the engines from the 9/11 aircraft?
quadratic equation solving mistake
Distance Matrix (plugin) - QGIS
No IMPLICIT_CONVERSION warning in this query plan
What is the legal status of travelling with (unprescribed) methadone in your carry-on?
Should my manager be aware of private LinkedIn approaches I receive? How to politely have this happen?
Change CPU MHz from Registry
Create a checkpoint in a foreach statement
How to create Excel (.XLS and .XLSX) file in C# without installing Ms Office?Find data cell value, move up one cell in column, find next data cell within a column and get next valueCreate Cells starting at date x and ending at date yExcel VBA: Having trouble understanding LoopsExcel VBA - Selection Routine'If … Then' statement with loopTranspose visible cells till last columnVBA IF statement Loop If values are equalExcel VBA macro to send emails to unique users in rangeHow can I add looping per 250 cells and offset the array?
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;
I am writing a code that put an X in a cell depending on a offset cell value, for exemple if the offset cell has a value of 3, it will put an X in the cell and decrement the offset cell value, i want to save the location of that cell and start the next for each with it.
For Each Cell In plage
If (Cell.Offset(0, 1).Value <> 0) Then
If (Cell.Value <> "X") Then
Cell.Offset(0, 1).Value = Cell.Offset(0, 1).Value - 1
Cell.Value = "X"
Checkpoint = Cell.Address
Exit For
Else
Cell.Value = ""
GoTo NextStep
End If
Exit For
Else
Cell.Value = ""
End If
NextStep:
Next Cell
The problem i am having with the current code is it start the loop all over again while i want it to keep till the end of the lines, until all offset value are equal to 0.
excel vba
asked Mar 25 at 10:22
Sonofliberty11Sonofliberty11
51 bronze badge
add a comment |
I am writing a code that put an X in a cell depending on a offset cell value, for exemple if the offset cell has a value of 3, it will put an X in the cell and decrement the offset cell value, i want to save the location of that cell and start the next for each with it.
For Each Cell In plage
If (Cell.Offset(0, 1).Value <> 0) Then
If (Cell.Value <> "X") Then
Cell.Offset(0, 1).Value = Cell.Offset(0, 1).Value - 1
Cell.Value = "X"
Checkpoint = Cell.Address
Exit For
Else
Cell.Value = ""
GoTo NextStep
End If
Exit For
Else
Cell.Value = ""
End If
NextStep:
Next Cell
The problem i am having with the current code is it start the loop all over again while i want it to keep till the end of the lines, until all offset value are equal to 0.
excel vba
asked Mar 25 at 10:22
Sonofliberty11Sonofliberty11
51 bronze badge
add a comment |
I am writing a code that put an X in a cell depending on a offset cell value, for exemple if the offset cell has a value of 3, it will put an X in the cell and decrement the offset cell value, i want to save the location of that cell and start the next for each with it.
For Each Cell In plage
If (Cell.Offset(0, 1).Value <> 0) Then
If (Cell.Value <> "X") Then
Cell.Offset(0, 1).Value = Cell.Offset(0, 1).Value - 1
Cell.Value = "X"
Checkpoint = Cell.Address
Exit For
Else
Cell.Value = ""
GoTo NextStep
End If
Exit For
Else
Cell.Value = ""
End If
NextStep:
Next Cell
The problem i am having with the current code is it start the loop all over again while i want it to keep till the end of the lines, until all offset value are equal to 0.
excel vba
asked Mar 25 at 10:22
Sonofliberty11Sonofliberty11
51 bronze badge
I am writing a code that put an X in a cell depending on a offset cell value, for exemple if the offset cell has a value of 3, it will put an X in the cell and decrement the offset cell value, i want to save the location of that cell and start the next for each with it.
For Each Cell In plage
If (Cell.Offset(0, 1).Value <> 0) Then
If (Cell.Value <> "X") Then
Cell.Offset(0, 1).Value = Cell.Offset(0, 1).Value - 1
Cell.Value = "X"
Checkpoint = Cell.Address
Exit For
Else
Cell.Value = ""
GoTo NextStep
End If
Exit For
Else
Cell.Value = ""
End If
NextStep:
Next Cell
The problem i am having with the current code is it start the loop all over again while i want it to keep till the end of the lines, until all offset value are equal to 0.
excel vba
excel vba
asked Mar 25 at 10:22
Sonofliberty11Sonofliberty11
51 bronze badge
asked Mar 25 at 10:22
Sonofliberty11Sonofliberty11
51 bronze badge
asked Mar 25 at 10:22
Sonofliberty11Sonofliberty11
51 bronze badge
asked Mar 25 at 10:22
Sonofliberty11Sonofliberty11
51 bronze badge
asked Mar 25 at 10:22
Sonofliberty11Sonofliberty11
51 bronze badge
51 bronze badge
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
Try the below (there are notes on the code). If you face difficulties let me know.
Option Explicit
Sub test()
'In this example we assume that the data you want to loop appear in Column A
Dim i As Long, Lastrow As Long
Dim Checkpoint As Variant
With ThisWorkbook.Worksheets("Sheet1") '<- Change sheet name if needed
Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row '< -Fins the lastrow of the column you want to loop
For i = 2 To Lastrow ' < -Start looping from row 2 to Lastrow fo the column
If .Range("A" & i).Offset(0, 1).Value <> 0 Then '<- You are looping
If .Range("A" & i).Value <> "X" Then
.Range("A" & i).Offset(0, 1).Value = .Range("A" & i).Offset(0, 1).Value - 1
.Range("A" & i).Value = .Range("A" & i).Value & "X"
Checkpoint = .Range("A" & i).Address
Else
.Range("A" & i).Value = ""
End If
Else
.Range("A" & i).Value = ""
End If
Next i
End With
End Sub
answered Mar 25 at 10:54
Error 1004Error 1004
4,8132 gold badges6 silver badges25 bronze badges
Thanks a lot for you code, i tried it and it worked, the problem is i want it to add one X each time i press the button
– Sonofliberty11
Mar 25 at 11:41
I have edited the answer. Could you please try again?
– Error 1004
Mar 25 at 12:32
I tried your eddited code, its better but it still put an X if he find a 0 in the offset cell, also i don't see the point of the checkpoint variable
– Sonofliberty11
Mar 25 at 15:06
add a comment |
Is plage a range?
If so, you could update it to start from the checkpoint and include all cells up to some lastCell for example.
Something like:
set plage=thisWorkbook.Worksheets("Your Worksheet").Range(checkpoint,lastCell)
That way the next For-Each should start from your checkpoint.
BTW if I understand correctly what you'e trying to do, I would suggest you replace cell.value="" with cell.clearContents
answered Mar 25 at 10:41
Stavros JonStavros Jon
8373 silver badges11 bronze badges
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%2f55335623%2fcreate-a-checkpoint-in-a-foreach-statement%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
Try the below (there are notes on the code). If you face difficulties let me know.
Option Explicit
Sub test()
'In this example we assume that the data you want to loop appear in Column A
Dim i As Long, Lastrow As Long
Dim Checkpoint As Variant
With ThisWorkbook.Worksheets("Sheet1") '<- Change sheet name if needed
Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row '< -Fins the lastrow of the column you want to loop
For i = 2 To Lastrow ' < -Start looping from row 2 to Lastrow fo the column
If .Range("A" & i).Offset(0, 1).Value <> 0 Then '<- You are looping
If .Range("A" & i).Value <> "X" Then
.Range("A" & i).Offset(0, 1).Value = .Range("A" & i).Offset(0, 1).Value - 1
.Range("A" & i).Value = .Range("A" & i).Value & "X"
Checkpoint = .Range("A" & i).Address
Else
.Range("A" & i).Value = ""
End If
Else
.Range("A" & i).Value = ""
End If
Next i
End With
End Sub
answered Mar 25 at 10:54
Error 1004Error 1004
4,8132 gold badges6 silver badges25 bronze badges
Thanks a lot for you code, i tried it and it worked, the problem is i want it to add one X each time i press the button
– Sonofliberty11
Mar 25 at 11:41
I have edited the answer. Could you please try again?
– Error 1004
Mar 25 at 12:32
I tried your eddited code, its better but it still put an X if he find a 0 in the offset cell, also i don't see the point of the checkpoint variable
– Sonofliberty11
Mar 25 at 15:06
add a comment |
Try the below (there are notes on the code). If you face difficulties let me know.
Option Explicit
Sub test()
'In this example we assume that the data you want to loop appear in Column A
Dim i As Long, Lastrow As Long
Dim Checkpoint As Variant
With ThisWorkbook.Worksheets("Sheet1") '<- Change sheet name if needed
Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row '< -Fins the lastrow of the column you want to loop
For i = 2 To Lastrow ' < -Start looping from row 2 to Lastrow fo the column
If .Range("A" & i).Offset(0, 1).Value <> 0 Then '<- You are looping
If .Range("A" & i).Value <> "X" Then
.Range("A" & i).Offset(0, 1).Value = .Range("A" & i).Offset(0, 1).Value - 1
.Range("A" & i).Value = .Range("A" & i).Value & "X"
Checkpoint = .Range("A" & i).Address
Else
.Range("A" & i).Value = ""
End If
Else
.Range("A" & i).Value = ""
End If
Next i
End With
End Sub
answered Mar 25 at 10:54
Error 1004Error 1004
4,8132 gold badges6 silver badges25 bronze badges
Thanks a lot for you code, i tried it and it worked, the problem is i want it to add one X each time i press the button
– Sonofliberty11
Mar 25 at 11:41
I have edited the answer. Could you please try again?
– Error 1004
Mar 25 at 12:32
I tried your eddited code, its better but it still put an X if he find a 0 in the offset cell, also i don't see the point of the checkpoint variable
– Sonofliberty11
Mar 25 at 15:06
add a comment |
Try the below (there are notes on the code). If you face difficulties let me know.
Option Explicit
Sub test()
'In this example we assume that the data you want to loop appear in Column A
Dim i As Long, Lastrow As Long
Dim Checkpoint As Variant
With ThisWorkbook.Worksheets("Sheet1") '<- Change sheet name if needed
Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row '< -Fins the lastrow of the column you want to loop
For i = 2 To Lastrow ' < -Start looping from row 2 to Lastrow fo the column
If .Range("A" & i).Offset(0, 1).Value <> 0 Then '<- You are looping
If .Range("A" & i).Value <> "X" Then
.Range("A" & i).Offset(0, 1).Value = .Range("A" & i).Offset(0, 1).Value - 1
.Range("A" & i).Value = .Range("A" & i).Value & "X"
Checkpoint = .Range("A" & i).Address
Else
.Range("A" & i).Value = ""
End If
Else
.Range("A" & i).Value = ""
End If
Next i
End With
End Sub
answered Mar 25 at 10:54
Error 1004Error 1004
4,8132 gold badges6 silver badges25 bronze badges
Try the below (there are notes on the code). If you face difficulties let me know.
Option Explicit
Sub test()
'In this example we assume that the data you want to loop appear in Column A
Dim i As Long, Lastrow As Long
Dim Checkpoint As Variant
With ThisWorkbook.Worksheets("Sheet1") '<- Change sheet name if needed
Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row '< -Fins the lastrow of the column you want to loop
For i = 2 To Lastrow ' < -Start looping from row 2 to Lastrow fo the column
If .Range("A" & i).Offset(0, 1).Value <> 0 Then '<- You are looping
If .Range("A" & i).Value <> "X" Then
.Range("A" & i).Offset(0, 1).Value = .Range("A" & i).Offset(0, 1).Value - 1
.Range("A" & i).Value = .Range("A" & i).Value & "X"
Checkpoint = .Range("A" & i).Address
Else
.Range("A" & i).Value = ""
End If
Else
.Range("A" & i).Value = ""
End If
Next i
End With
End Sub
answered Mar 25 at 10:54
Error 1004Error 1004
4,8132 gold badges6 silver badges25 bronze badges
edited Mar 25 at 12:32
answered Mar 25 at 10:54
Error 1004Error 1004
4,8132 gold badges6 silver badges25 bronze badges
answered Mar 25 at 10:54
Error 1004Error 1004
4,8132 gold badges6 silver badges25 bronze badges
answered Mar 25 at 10:54
Error 1004Error 1004
4,8132 gold badges6 silver badges25 bronze badges
4,8132 gold badges6 silver badges25 bronze badges
Thanks a lot for you code, i tried it and it worked, the problem is i want it to add one X each time i press the button
– Sonofliberty11
Mar 25 at 11:41
I have edited the answer. Could you please try again?
– Error 1004
Mar 25 at 12:32
I tried your eddited code, its better but it still put an X if he find a 0 in the offset cell, also i don't see the point of the checkpoint variable
– Sonofliberty11
Mar 25 at 15:06
add a comment |
Thanks a lot for you code, i tried it and it worked, the problem is i want it to add one X each time i press the button
– Sonofliberty11
Mar 25 at 11:41
I have edited the answer. Could you please try again?
– Error 1004
Mar 25 at 12:32
I tried your eddited code, its better but it still put an X if he find a 0 in the offset cell, also i don't see the point of the checkpoint variable
– Sonofliberty11
Mar 25 at 15:06
Thanks a lot for you code, i tried it and it worked, the problem is i want it to add one X each time i press the button
– Sonofliberty11
Mar 25 at 11:41
Thanks a lot for you code, i tried it and it worked, the problem is i want it to add one X each time i press the button
– Sonofliberty11
Mar 25 at 11:41
I have edited the answer. Could you please try again?
– Error 1004
Mar 25 at 12:32
I have edited the answer. Could you please try again?
– Error 1004
Mar 25 at 12:32
I tried your eddited code, its better but it still put an X if he find a 0 in the offset cell, also i don't see the point of the checkpoint variable
– Sonofliberty11
Mar 25 at 15:06
I tried your eddited code, its better but it still put an X if he find a 0 in the offset cell, also i don't see the point of the checkpoint variable
– Sonofliberty11
Mar 25 at 15:06
add a comment |
Is plage a range?
If so, you could update it to start from the checkpoint and include all cells up to some lastCell for example.
Something like:
set plage=thisWorkbook.Worksheets("Your Worksheet").Range(checkpoint,lastCell)
That way the next For-Each should start from your checkpoint.
BTW if I understand correctly what you'e trying to do, I would suggest you replace cell.value="" with cell.clearContents
answered Mar 25 at 10:41
Stavros JonStavros Jon
8373 silver badges11 bronze badges
add a comment |
Is plage a range?
If so, you could update it to start from the checkpoint and include all cells up to some lastCell for example.
Something like:
set plage=thisWorkbook.Worksheets("Your Worksheet").Range(checkpoint,lastCell)
That way the next For-Each should start from your checkpoint.
BTW if I understand correctly what you'e trying to do, I would suggest you replace cell.value="" with cell.clearContents
answered Mar 25 at 10:41
Stavros JonStavros Jon
8373 silver badges11 bronze badges
add a comment |
Is plage a range?
If so, you could update it to start from the checkpoint and include all cells up to some lastCell for example.
Something like:
set plage=thisWorkbook.Worksheets("Your Worksheet").Range(checkpoint,lastCell)
That way the next For-Each should start from your checkpoint.
BTW if I understand correctly what you'e trying to do, I would suggest you replace cell.value="" with cell.clearContents
answered Mar 25 at 10:41
Stavros JonStavros Jon
8373 silver badges11 bronze badges
Is plage a range?
If so, you could update it to start from the checkpoint and include all cells up to some lastCell for example.
Something like:
set plage=thisWorkbook.Worksheets("Your Worksheet").Range(checkpoint,lastCell)
That way the next For-Each should start from your checkpoint.
BTW if I understand correctly what you'e trying to do, I would suggest you replace cell.value="" with cell.clearContents
answered Mar 25 at 10:41
Stavros JonStavros Jon
8373 silver badges11 bronze badges
answered Mar 25 at 10:41
Stavros JonStavros Jon
8373 silver badges11 bronze badges
answered Mar 25 at 10:41
Stavros JonStavros Jon
8373 silver badges11 bronze badges
answered Mar 25 at 10:41
Stavros JonStavros Jon
8373 silver badges11 bronze badges
8373 silver badges11 bronze badges
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%2f55335623%2fcreate-a-checkpoint-in-a-foreach-statement%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
