Excel VBA, Facing some issues
0
I have this code:
For Search_Library = 0 To 200
j = Search_Library * 20
If Worksheets("Config Algemeen").Range("H2").offset(Search_Config, 0).Value = Worksheets("Library").Range("A2").offset(j, 0).Value Then
For k = 1 To 200
l = k * 2
For m = 0 To 19
MyChoices(m) = Worksheets("Library").Range("A2").offset(j + m, l).Value
Next m
With Worksheets("Config Algemeen").Range("H2").offset(Search_Config, k).Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:=Join(MyChoices, ",")
End With
Next k
Else
'Nothing
End If
Next Search_Library
Now I'm facing a problem: I want to loop through "Search_Config" each time when "Search_Library" reach a value of 200 (max value). What is the best way to loop through "Search_Config" as well? Basically I want to do add 1 (+1) to "Search_Config" and start from zero (0) with "Search_Library".
excel vba
edited Nov 14 '18 at 10:36
Ron Rosenfeld
23.4k41636
asked Nov 14 '18 at 10:30
Mattijn StegemanMattijn Stegeman
134
|
show 2 more comments
0
I have this code:
For Search_Library = 0 To 200
j = Search_Library * 20
If Worksheets("Config Algemeen").Range("H2").offset(Search_Config, 0).Value = Worksheets("Library").Range("A2").offset(j, 0).Value Then
For k = 1 To 200
l = k * 2
For m = 0 To 19
MyChoices(m) = Worksheets("Library").Range("A2").offset(j + m, l).Value
Next m
With Worksheets("Config Algemeen").Range("H2").offset(Search_Config, k).Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:=Join(MyChoices, ",")
End With
Next k
Else
'Nothing
End If
Next Search_Library
Now I'm facing a problem: I want to loop through "Search_Config" each time when "Search_Library" reach a value of 200 (max value). What is the best way to loop through "Search_Config" as well? Basically I want to do add 1 (+1) to "Search_Config" and start from zero (0) with "Search_Library".
excel vba
edited Nov 14 '18 at 10:36
Ron Rosenfeld
23.4k41636
asked Nov 14 '18 at 10:30
Mattijn StegemanMattijn Stegeman
134
What have you tried? It would seem simple to enclose your existing loop within aFor Search_Config = x to y … Next Search_Configloop.
– Ron Rosenfeld
Nov 14 '18 at 10:44
@RonRosenfeld That's what I thought... But Excel seems to crash (no responce). Excel gives me an "Offset failure".
– Mattijn Stegeman
Nov 14 '18 at 10:47
I've never seen that Excel error. and a Google search is unrevealing. So the error must be coming from something else in your system.
– Ron Rosenfeld
Nov 14 '18 at 10:54
@RonRosenfeld Method Of Range Class Failed
– Mattijn Stegeman
Nov 14 '18 at 10:57
That is a VBA error message. Is that the Exact message? Usually that type of message refers to a specific method. What line causes the error? What are the values of the different parameters in that line when the error occurs? That's some of what you need to troubleshoot the problem.
– Ron Rosenfeld
Nov 14 '18 at 11:06
|
show 2 more comments
0
0
0
I have this code:
For Search_Library = 0 To 200
j = Search_Library * 20
If Worksheets("Config Algemeen").Range("H2").offset(Search_Config, 0).Value = Worksheets("Library").Range("A2").offset(j, 0).Value Then
For k = 1 To 200
l = k * 2
For m = 0 To 19
MyChoices(m) = Worksheets("Library").Range("A2").offset(j + m, l).Value
Next m
With Worksheets("Config Algemeen").Range("H2").offset(Search_Config, k).Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:=Join(MyChoices, ",")
End With
Next k
Else
'Nothing
End If
Next Search_Library
Now I'm facing a problem: I want to loop through "Search_Config" each time when "Search_Library" reach a value of 200 (max value). What is the best way to loop through "Search_Config" as well? Basically I want to do add 1 (+1) to "Search_Config" and start from zero (0) with "Search_Library".
excel vba
edited Nov 14 '18 at 10:36
Ron Rosenfeld
23.4k41636
asked Nov 14 '18 at 10:30
Mattijn StegemanMattijn Stegeman
134
I have this code:
For Search_Library = 0 To 200
j = Search_Library * 20
If Worksheets("Config Algemeen").Range("H2").offset(Search_Config, 0).Value = Worksheets("Library").Range("A2").offset(j, 0).Value Then
For k = 1 To 200
l = k * 2
For m = 0 To 19
MyChoices(m) = Worksheets("Library").Range("A2").offset(j + m, l).Value
Next m
With Worksheets("Config Algemeen").Range("H2").offset(Search_Config, k).Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:=Join(MyChoices, ",")
End With
Next k
Else
'Nothing
End If
Next Search_Library
Now I'm facing a problem: I want to loop through "Search_Config" each time when "Search_Library" reach a value of 200 (max value). What is the best way to loop through "Search_Config" as well? Basically I want to do add 1 (+1) to "Search_Config" and start from zero (0) with "Search_Library".
excel vba
excel vba
edited Nov 14 '18 at 10:36
Ron Rosenfeld
23.4k41636
asked Nov 14 '18 at 10:30
Mattijn StegemanMattijn Stegeman
134
edited Nov 14 '18 at 10:36
Ron Rosenfeld
23.4k41636
asked Nov 14 '18 at 10:30
Mattijn StegemanMattijn Stegeman
134
edited Nov 14 '18 at 10:36
Ron Rosenfeld
23.4k41636
edited Nov 14 '18 at 10:36
Ron Rosenfeld
23.4k41636
edited Nov 14 '18 at 10:36
Ron Rosenfeld
23.4k41636
23.4k41636
asked Nov 14 '18 at 10:30
Mattijn StegemanMattijn Stegeman
134
asked Nov 14 '18 at 10:30
Mattijn StegemanMattijn Stegeman
134
asked Nov 14 '18 at 10:30
Mattijn StegemanMattijn Stegeman
134
134
What have you tried? It would seem simple to enclose your existing loop within aFor Search_Config = x to y … Next Search_Configloop.
– Ron Rosenfeld
Nov 14 '18 at 10:44
@RonRosenfeld That's what I thought... But Excel seems to crash (no responce). Excel gives me an "Offset failure".
– Mattijn Stegeman
Nov 14 '18 at 10:47
I've never seen that Excel error. and a Google search is unrevealing. So the error must be coming from something else in your system.
– Ron Rosenfeld
Nov 14 '18 at 10:54
@RonRosenfeld Method Of Range Class Failed
– Mattijn Stegeman
Nov 14 '18 at 10:57
That is a VBA error message. Is that the Exact message? Usually that type of message refers to a specific method. What line causes the error? What are the values of the different parameters in that line when the error occurs? That's some of what you need to troubleshoot the problem.
– Ron Rosenfeld
Nov 14 '18 at 11:06
|
show 2 more comments
What have you tried? It would seem simple to enclose your existing loop within aFor Search_Config = x to y … Next Search_Configloop.
– Ron Rosenfeld
Nov 14 '18 at 10:44
@RonRosenfeld That's what I thought... But Excel seems to crash (no responce). Excel gives me an "Offset failure".
– Mattijn Stegeman
Nov 14 '18 at 10:47
I've never seen that Excel error. and a Google search is unrevealing. So the error must be coming from something else in your system.
– Ron Rosenfeld
Nov 14 '18 at 10:54
@RonRosenfeld Method Of Range Class Failed
– Mattijn Stegeman
Nov 14 '18 at 10:57
That is a VBA error message. Is that the Exact message? Usually that type of message refers to a specific method. What line causes the error? What are the values of the different parameters in that line when the error occurs? That's some of what you need to troubleshoot the problem.
– Ron Rosenfeld
Nov 14 '18 at 11:06
What have you tried? It would seem simple to enclose your existing loop within a
For Search_Config = x to y … Next Search_Config loop.– Ron Rosenfeld
Nov 14 '18 at 10:44
What have you tried? It would seem simple to enclose your existing loop within a
For Search_Config = x to y … Next Search_Config loop.– Ron Rosenfeld
Nov 14 '18 at 10:44
@RonRosenfeld That's what I thought... But Excel seems to crash (no responce). Excel gives me an "Offset failure".
– Mattijn Stegeman
Nov 14 '18 at 10:47
@RonRosenfeld That's what I thought... But Excel seems to crash (no responce). Excel gives me an "Offset failure".
– Mattijn Stegeman
Nov 14 '18 at 10:47
I've never seen that Excel error. and a Google search is unrevealing. So the error must be coming from something else in your system.
– Ron Rosenfeld
Nov 14 '18 at 10:54
I've never seen that Excel error. and a Google search is unrevealing. So the error must be coming from something else in your system.
– Ron Rosenfeld
Nov 14 '18 at 10:54
@RonRosenfeld Method Of Range Class Failed
– Mattijn Stegeman
Nov 14 '18 at 10:57
@RonRosenfeld Method Of Range Class Failed
– Mattijn Stegeman
Nov 14 '18 at 10:57
That is a VBA error message. Is that the Exact message? Usually that type of message refers to a specific method. What line causes the error? What are the values of the different parameters in that line when the error occurs? That's some of what you need to troubleshoot the problem.
– Ron Rosenfeld
Nov 14 '18 at 11:06
That is a VBA error message. Is that the Exact message? Usually that type of message refers to a specific method. What line causes the error? What are the values of the different parameters in that line when the error occurs? That's some of what you need to troubleshoot the problem.
– Ron Rosenfeld
Nov 14 '18 at 11:06
|
show 2 more comments
1 Answer
1
active
oldest
votes
0
For Search_Config = 0 To 200
For Search_Library = 0 To 200
j = Search_Library * 20
If (Worksheets("Config Algemeen").Range("H2").offset(Search_Config, 0).Value = Worksheets("Library").Range("A2").offset(j, 0).Value) And Not (Worksheets("Config Algemeen").Range("H2").offset(Search_Config, 0).Value = "") Then
For k = 1 To 200
l = k * 2
n = l - 1
For m = 0 To 19
MyChoices(m) = Worksheets("Library").Range("A2").offset(j + m, l).Value
Next m
With Worksheets("Config Algemeen").Range("H2").offset(Search_Config, k)
If Not Worksheets("Library").Cells(2 + j, 1 + n).Value = "" Then
.AddComment Worksheets("Library").Cells(2 + j, 1 + n).Value
Else
'Nothing
End If
If Worksheets("Library").Range("A2").offset(j, l).Value = "*VALUE*" Or Worksheets("Library").Range("A2").offset(j, l).Value = "" Then
'Nothing
Else
.Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:=Join(MyChoices, ",")
End If
End With
Next k
Else
'Nothing
End If
Next Search_Library
Next Search_Config
It's working like this now. And everything seems fine. It's quick and solit as rock. Thank you for helping me! It brought me on the right track.
answered Nov 16 '18 at 8:50
Mattijn StegemanMattijn Stegeman
134
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%2f53298038%2fexcel-vba-facing-some-issues%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
0
For Search_Config = 0 To 200
For Search_Library = 0 To 200
j = Search_Library * 20
If (Worksheets("Config Algemeen").Range("H2").offset(Search_Config, 0).Value = Worksheets("Library").Range("A2").offset(j, 0).Value) And Not (Worksheets("Config Algemeen").Range("H2").offset(Search_Config, 0).Value = "") Then
For k = 1 To 200
l = k * 2
n = l - 1
For m = 0 To 19
MyChoices(m) = Worksheets("Library").Range("A2").offset(j + m, l).Value
Next m
With Worksheets("Config Algemeen").Range("H2").offset(Search_Config, k)
If Not Worksheets("Library").Cells(2 + j, 1 + n).Value = "" Then
.AddComment Worksheets("Library").Cells(2 + j, 1 + n).Value
Else
'Nothing
End If
If Worksheets("Library").Range("A2").offset(j, l).Value = "*VALUE*" Or Worksheets("Library").Range("A2").offset(j, l).Value = "" Then
'Nothing
Else
.Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:=Join(MyChoices, ",")
End If
End With
Next k
Else
'Nothing
End If
Next Search_Library
Next Search_Config
It's working like this now. And everything seems fine. It's quick and solit as rock. Thank you for helping me! It brought me on the right track.
answered Nov 16 '18 at 8:50
Mattijn StegemanMattijn Stegeman
134
add a comment |
0
For Search_Config = 0 To 200
For Search_Library = 0 To 200
j = Search_Library * 20
If (Worksheets("Config Algemeen").Range("H2").offset(Search_Config, 0).Value = Worksheets("Library").Range("A2").offset(j, 0).Value) And Not (Worksheets("Config Algemeen").Range("H2").offset(Search_Config, 0).Value = "") Then
For k = 1 To 200
l = k * 2
n = l - 1
For m = 0 To 19
MyChoices(m) = Worksheets("Library").Range("A2").offset(j + m, l).Value
Next m
With Worksheets("Config Algemeen").Range("H2").offset(Search_Config, k)
If Not Worksheets("Library").Cells(2 + j, 1 + n).Value = "" Then
.AddComment Worksheets("Library").Cells(2 + j, 1 + n).Value
Else
'Nothing
End If
If Worksheets("Library").Range("A2").offset(j, l).Value = "*VALUE*" Or Worksheets("Library").Range("A2").offset(j, l).Value = "" Then
'Nothing
Else
.Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:=Join(MyChoices, ",")
End If
End With
Next k
Else
'Nothing
End If
Next Search_Library
Next Search_Config
It's working like this now. And everything seems fine. It's quick and solit as rock. Thank you for helping me! It brought me on the right track.
answered Nov 16 '18 at 8:50
Mattijn StegemanMattijn Stegeman
134
add a comment |
0
0
0
For Search_Config = 0 To 200
For Search_Library = 0 To 200
j = Search_Library * 20
If (Worksheets("Config Algemeen").Range("H2").offset(Search_Config, 0).Value = Worksheets("Library").Range("A2").offset(j, 0).Value) And Not (Worksheets("Config Algemeen").Range("H2").offset(Search_Config, 0).Value = "") Then
For k = 1 To 200
l = k * 2
n = l - 1
For m = 0 To 19
MyChoices(m) = Worksheets("Library").Range("A2").offset(j + m, l).Value
Next m
With Worksheets("Config Algemeen").Range("H2").offset(Search_Config, k)
If Not Worksheets("Library").Cells(2 + j, 1 + n).Value = "" Then
.AddComment Worksheets("Library").Cells(2 + j, 1 + n).Value
Else
'Nothing
End If
If Worksheets("Library").Range("A2").offset(j, l).Value = "*VALUE*" Or Worksheets("Library").Range("A2").offset(j, l).Value = "" Then
'Nothing
Else
.Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:=Join(MyChoices, ",")
End If
End With
Next k
Else
'Nothing
End If
Next Search_Library
Next Search_Config
It's working like this now. And everything seems fine. It's quick and solit as rock. Thank you for helping me! It brought me on the right track.
answered Nov 16 '18 at 8:50
Mattijn StegemanMattijn Stegeman
134
For Search_Config = 0 To 200
For Search_Library = 0 To 200
j = Search_Library * 20
If (Worksheets("Config Algemeen").Range("H2").offset(Search_Config, 0).Value = Worksheets("Library").Range("A2").offset(j, 0).Value) And Not (Worksheets("Config Algemeen").Range("H2").offset(Search_Config, 0).Value = "") Then
For k = 1 To 200
l = k * 2
n = l - 1
For m = 0 To 19
MyChoices(m) = Worksheets("Library").Range("A2").offset(j + m, l).Value
Next m
With Worksheets("Config Algemeen").Range("H2").offset(Search_Config, k)
If Not Worksheets("Library").Cells(2 + j, 1 + n).Value = "" Then
.AddComment Worksheets("Library").Cells(2 + j, 1 + n).Value
Else
'Nothing
End If
If Worksheets("Library").Range("A2").offset(j, l).Value = "*VALUE*" Or Worksheets("Library").Range("A2").offset(j, l).Value = "" Then
'Nothing
Else
.Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:=Join(MyChoices, ",")
End If
End With
Next k
Else
'Nothing
End If
Next Search_Library
Next Search_Config
It's working like this now. And everything seems fine. It's quick and solit as rock. Thank you for helping me! It brought me on the right track.
answered Nov 16 '18 at 8:50
Mattijn StegemanMattijn Stegeman
134
answered Nov 16 '18 at 8:50
Mattijn StegemanMattijn Stegeman
134
answered Nov 16 '18 at 8:50
Mattijn StegemanMattijn Stegeman
134
answered Nov 16 '18 at 8:50
Mattijn StegemanMattijn Stegeman
134
134
add a comment |
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%2f53298038%2fexcel-vba-facing-some-issues%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
What have you tried? It would seem simple to enclose your existing loop within a
For Search_Config = x to y … Next Search_Configloop.– Ron Rosenfeld
Nov 14 '18 at 10:44
@RonRosenfeld That's what I thought... But Excel seems to crash (no responce). Excel gives me an "Offset failure".
– Mattijn Stegeman
Nov 14 '18 at 10:47
I've never seen that Excel error. and a Google search is unrevealing. So the error must be coming from something else in your system.
– Ron Rosenfeld
Nov 14 '18 at 10:54
@RonRosenfeld Method Of Range Class Failed
– Mattijn Stegeman
Nov 14 '18 at 10:57
That is a VBA error message. Is that the Exact message? Usually that type of message refers to a specific method. What line causes the error? What are the values of the different parameters in that line when the error occurs? That's some of what you need to troubleshoot the problem.
– Ron Rosenfeld
Nov 14 '18 at 11:06