VBA input box with loop and step
Hi I have the following code below which works but as you can see is very manual in terms of the loop until the input entry is correct. Basically I want the code to check that the number the user has entered equals 19 or any row number from 19 with a step /increment of 7 e.g. 26,33,40 etc. up to a maximum of 1002 so in effect my validation range is 19 to 1002, with step of 7 from 19. I have removed some of the myvalue code for the purposes of this post to reduce size. Any help would be appreciated, thanks.
sub InsertRows()
Dim lastRow As Long
Dim Row1 As Long
Dim Row2 As Long
Dim myvalue As Variant
Dim i As Long
Dim CancelTest As Variant
Dim Row As Range
Dim myPassword As String
myPassword = "Password"
Application.ScreenUpdating = False
lastRow = 0
Do
myvalue = InputBox("Insert Rows Starting From Input Number:" & Chr(10) & _
"e.g. 19, 26, 33 (Multiples of 7)")
If StrPtr(myvalue) = 0 Then Exit Sub
If Not IsNumeric(myvalue) Then MsgBox "Numeric Values Only" & Chr(10) & _
"Starting From Row 19 In Multiples Of 7"
Loop Until Val(myvalue) = 19 Or myvalue = 26 Or myvalue = 33 Or myvalue = 40 Or myvalue = 47 Or myvalue = 54 Or myvalue = 61 Or myvalue = 68 Or myvalue = 75 Or myvalue = 82 Or myvalue = 89 Or myvalue = 96 Or myvalue = 103
If MsgBox("Are you sure?", vbYesNo) = vbNo Then Exit Sub
With Sheet1
.Select
.Unprotect Password:=myPassword
ActiveSheet.Outline.ShowLevels RowLevels:=2
lastRow = Cells(Rows.Count, "B").End(xlUp).Row
Row1 = lastRow - 6
Row2 = lastRow
Rows(Row1 & ":" & Row2).Select
Selection.Copy
End With
With Sheet1
.Select
Range("a" & myvalue).Select
Selection.Insert Shift:=xlDown
On Error GoTo 0
Application.CutCopyMode = False
lastRow = 0
.Range("c11").Select
.Protect Password:=myPassword, AllowFiltering:=True, AllowFormattingCells:=True, DrawingObjects:=False, Contents:=True, UserInterfaceOnly:=True, AllowFormattingRows:=True, AllowFormattingColumns:=True
End With
Application.ScreenUpdating = True
End Sub
excel
asked Nov 21 '18 at 9:36
Abvlecxe
32
add a comment |
Hi I have the following code below which works but as you can see is very manual in terms of the loop until the input entry is correct. Basically I want the code to check that the number the user has entered equals 19 or any row number from 19 with a step /increment of 7 e.g. 26,33,40 etc. up to a maximum of 1002 so in effect my validation range is 19 to 1002, with step of 7 from 19. I have removed some of the myvalue code for the purposes of this post to reduce size. Any help would be appreciated, thanks.
sub InsertRows()
Dim lastRow As Long
Dim Row1 As Long
Dim Row2 As Long
Dim myvalue As Variant
Dim i As Long
Dim CancelTest As Variant
Dim Row As Range
Dim myPassword As String
myPassword = "Password"
Application.ScreenUpdating = False
lastRow = 0
Do
myvalue = InputBox("Insert Rows Starting From Input Number:" & Chr(10) & _
"e.g. 19, 26, 33 (Multiples of 7)")
If StrPtr(myvalue) = 0 Then Exit Sub
If Not IsNumeric(myvalue) Then MsgBox "Numeric Values Only" & Chr(10) & _
"Starting From Row 19 In Multiples Of 7"
Loop Until Val(myvalue) = 19 Or myvalue = 26 Or myvalue = 33 Or myvalue = 40 Or myvalue = 47 Or myvalue = 54 Or myvalue = 61 Or myvalue = 68 Or myvalue = 75 Or myvalue = 82 Or myvalue = 89 Or myvalue = 96 Or myvalue = 103
If MsgBox("Are you sure?", vbYesNo) = vbNo Then Exit Sub
With Sheet1
.Select
.Unprotect Password:=myPassword
ActiveSheet.Outline.ShowLevels RowLevels:=2
lastRow = Cells(Rows.Count, "B").End(xlUp).Row
Row1 = lastRow - 6
Row2 = lastRow
Rows(Row1 & ":" & Row2).Select
Selection.Copy
End With
With Sheet1
.Select
Range("a" & myvalue).Select
Selection.Insert Shift:=xlDown
On Error GoTo 0
Application.CutCopyMode = False
lastRow = 0
.Range("c11").Select
.Protect Password:=myPassword, AllowFiltering:=True, AllowFormattingCells:=True, DrawingObjects:=False, Contents:=True, UserInterfaceOnly:=True, AllowFormattingRows:=True, AllowFormattingColumns:=True
End With
Application.ScreenUpdating = True
End Sub
excel
asked Nov 21 '18 at 9:36
Abvlecxe
32
add a comment |
Hi I have the following code below which works but as you can see is very manual in terms of the loop until the input entry is correct. Basically I want the code to check that the number the user has entered equals 19 or any row number from 19 with a step /increment of 7 e.g. 26,33,40 etc. up to a maximum of 1002 so in effect my validation range is 19 to 1002, with step of 7 from 19. I have removed some of the myvalue code for the purposes of this post to reduce size. Any help would be appreciated, thanks.
sub InsertRows()
Dim lastRow As Long
Dim Row1 As Long
Dim Row2 As Long
Dim myvalue As Variant
Dim i As Long
Dim CancelTest As Variant
Dim Row As Range
Dim myPassword As String
myPassword = "Password"
Application.ScreenUpdating = False
lastRow = 0
Do
myvalue = InputBox("Insert Rows Starting From Input Number:" & Chr(10) & _
"e.g. 19, 26, 33 (Multiples of 7)")
If StrPtr(myvalue) = 0 Then Exit Sub
If Not IsNumeric(myvalue) Then MsgBox "Numeric Values Only" & Chr(10) & _
"Starting From Row 19 In Multiples Of 7"
Loop Until Val(myvalue) = 19 Or myvalue = 26 Or myvalue = 33 Or myvalue = 40 Or myvalue = 47 Or myvalue = 54 Or myvalue = 61 Or myvalue = 68 Or myvalue = 75 Or myvalue = 82 Or myvalue = 89 Or myvalue = 96 Or myvalue = 103
If MsgBox("Are you sure?", vbYesNo) = vbNo Then Exit Sub
With Sheet1
.Select
.Unprotect Password:=myPassword
ActiveSheet.Outline.ShowLevels RowLevels:=2
lastRow = Cells(Rows.Count, "B").End(xlUp).Row
Row1 = lastRow - 6
Row2 = lastRow
Rows(Row1 & ":" & Row2).Select
Selection.Copy
End With
With Sheet1
.Select
Range("a" & myvalue).Select
Selection.Insert Shift:=xlDown
On Error GoTo 0
Application.CutCopyMode = False
lastRow = 0
.Range("c11").Select
.Protect Password:=myPassword, AllowFiltering:=True, AllowFormattingCells:=True, DrawingObjects:=False, Contents:=True, UserInterfaceOnly:=True, AllowFormattingRows:=True, AllowFormattingColumns:=True
End With
Application.ScreenUpdating = True
End Sub
excel
asked Nov 21 '18 at 9:36
Abvlecxe
32
Hi I have the following code below which works but as you can see is very manual in terms of the loop until the input entry is correct. Basically I want the code to check that the number the user has entered equals 19 or any row number from 19 with a step /increment of 7 e.g. 26,33,40 etc. up to a maximum of 1002 so in effect my validation range is 19 to 1002, with step of 7 from 19. I have removed some of the myvalue code for the purposes of this post to reduce size. Any help would be appreciated, thanks.
sub InsertRows()
Dim lastRow As Long
Dim Row1 As Long
Dim Row2 As Long
Dim myvalue As Variant
Dim i As Long
Dim CancelTest As Variant
Dim Row As Range
Dim myPassword As String
myPassword = "Password"
Application.ScreenUpdating = False
lastRow = 0
Do
myvalue = InputBox("Insert Rows Starting From Input Number:" & Chr(10) & _
"e.g. 19, 26, 33 (Multiples of 7)")
If StrPtr(myvalue) = 0 Then Exit Sub
If Not IsNumeric(myvalue) Then MsgBox "Numeric Values Only" & Chr(10) & _
"Starting From Row 19 In Multiples Of 7"
Loop Until Val(myvalue) = 19 Or myvalue = 26 Or myvalue = 33 Or myvalue = 40 Or myvalue = 47 Or myvalue = 54 Or myvalue = 61 Or myvalue = 68 Or myvalue = 75 Or myvalue = 82 Or myvalue = 89 Or myvalue = 96 Or myvalue = 103
If MsgBox("Are you sure?", vbYesNo) = vbNo Then Exit Sub
With Sheet1
.Select
.Unprotect Password:=myPassword
ActiveSheet.Outline.ShowLevels RowLevels:=2
lastRow = Cells(Rows.Count, "B").End(xlUp).Row
Row1 = lastRow - 6
Row2 = lastRow
Rows(Row1 & ":" & Row2).Select
Selection.Copy
End With
With Sheet1
.Select
Range("a" & myvalue).Select
Selection.Insert Shift:=xlDown
On Error GoTo 0
Application.CutCopyMode = False
lastRow = 0
.Range("c11").Select
.Protect Password:=myPassword, AllowFiltering:=True, AllowFormattingCells:=True, DrawingObjects:=False, Contents:=True, UserInterfaceOnly:=True, AllowFormattingRows:=True, AllowFormattingColumns:=True
End With
Application.ScreenUpdating = True
End Sub
excel
excel
asked Nov 21 '18 at 9:36
Abvlecxe
32
asked Nov 21 '18 at 9:36
Abvlecxe
32
asked Nov 21 '18 at 9:36
Abvlecxe
32
asked Nov 21 '18 at 9:36
Abvlecxe
32
asked Nov 21 '18 at 9:36
Abvlecxe
32
32
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
Using the Mod Operator
=Loop Until Val((myvalue - 19) Mod 7)) = 0
answered Nov 21 '18 at 10:30
Michal Rosa
1,3161814
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%2f53409049%2fvba-input-box-with-loop-and-step%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
Using the Mod Operator
=Loop Until Val((myvalue - 19) Mod 7)) = 0
answered Nov 21 '18 at 10:30
Michal Rosa
1,3161814
add a comment |
Using the Mod Operator
=Loop Until Val((myvalue - 19) Mod 7)) = 0
answered Nov 21 '18 at 10:30
Michal Rosa
1,3161814
add a comment |
Using the Mod Operator
=Loop Until Val((myvalue - 19) Mod 7)) = 0
answered Nov 21 '18 at 10:30
Michal Rosa
1,3161814
Using the Mod Operator
=Loop Until Val((myvalue - 19) Mod 7)) = 0
answered Nov 21 '18 at 10:30
Michal Rosa
1,3161814
answered Nov 21 '18 at 10:30
Michal Rosa
1,3161814
answered Nov 21 '18 at 10:30
Michal Rosa
1,3161814
answered Nov 21 '18 at 10:30
Michal Rosa
1,3161814
1,3161814
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.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- 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%2f53409049%2fvba-input-box-with-loop-and-step%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
