Faster alternative to multiple loops in VBAExtract Excel data using Interop.Excel from C#Sorting and...
Groups acting on trees
Can a dragon be stuck looking like a human?
How to prevent cleaner from hanging my lock screen in Ubuntu 16.04
Can we use the stored gravitational potential energy of a building to produce power?
Grade 10 Analytic Geometry Question 23- Incredibly hard
How to tag distinct options/entities without giving any an implicit priority or suggested order?
Isn't using the Extrusion Multiplier like cheating?
What's a good word to describe a public place that looks like it wouldn't be rough?
Is it a fallacy if someone claims they need an explanation for every word of your argument to the point where they don't understand common terms?
We are very unlucky in my court
Is there any differences between "Gucken" and "Schauen"?
Why did other German political parties disband so fast when Hitler was appointed chancellor?
Quenching swords in dragon blood; why?
How do I say "Brexit" in Latin?
Jumping Numbers
Every character has a name - does this lead to too many named characters?
What does Cypher mean when he says Neo is "gonna pop"?
What is the wife of a henpecked husband called?
Can a person refuse a presidential pardon?
Slow moving projectiles from a hand-held weapon - how do they reach the target?
Is a debit card dangerous for an account with low balance and no overdraft protection?
What flying insects could re-enter the Earth's atmosphere from space without burning up?
How to acknowledge an embarrassing job interview, now that I work directly with the interviewer?
How to deal with an incendiary email that was recalled
Faster alternative to multiple loops in VBA
Extract Excel data using Interop.Excel from C#Sorting and color-coding based on variablesRacetrack in… VBA?Refer to other cells besides the one in the Cells.FindMaking a report from payroll detailsAdvanced multiple criteria filter VBAExcel VBA script – concatenates multiple values using LoopVBA faster calculatingPull together information from many workbooks based on headersExcel 2016 VBA - Show/Hide multiple shape groups
$begingroup$
My code takes 3 or 4 minutes to run and I want to make it more efficient. I've attached a sample of one of many loops I am using which I think are slowing it down due to needless iterations.
Dim i As Long
For i = 5 To endrow
'does a vlookup in CAIM file
Acc = Application.Cells(i, 5)
Cname = Application.Cells(i, 4)
Acname = Application.VLookup(Acc, Sheet3.Range("D2:F299090"), 3, False)
If IsError(Acname) Then
Cells(i, 42).Value = ""
Else
Cells(i, 42).Value = Acname
End If
I have a lot of similar code to the above which obviously loops through every cell (about 15k rows and I do similar procedures for several columns). Would using arrays be much quicker, for example Dim Acc(0 To x) and loop through that way?
Also, would using error handling such as On Error Resume Next be significantly quicker than checking each variable using IsError()? I tried that when I first wrote the code but couldn't get it working properly.
Thank you in advance for any help at all
vba excel
asked 13 hours ago
edevedev
1
New contributor
edev is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
$endgroup$
add a comment |
$begingroup$
My code takes 3 or 4 minutes to run and I want to make it more efficient. I've attached a sample of one of many loops I am using which I think are slowing it down due to needless iterations.
Dim i As Long
For i = 5 To endrow
'does a vlookup in CAIM file
Acc = Application.Cells(i, 5)
Cname = Application.Cells(i, 4)
Acname = Application.VLookup(Acc, Sheet3.Range("D2:F299090"), 3, False)
If IsError(Acname) Then
Cells(i, 42).Value = ""
Else
Cells(i, 42).Value = Acname
End If
I have a lot of similar code to the above which obviously loops through every cell (about 15k rows and I do similar procedures for several columns). Would using arrays be much quicker, for example Dim Acc(0 To x) and loop through that way?
Also, would using error handling such as On Error Resume Next be significantly quicker than checking each variable using IsError()? I tried that when I first wrote the code but couldn't get it working properly.
Thank you in advance for any help at all
vba excel
asked 13 hours ago
edevedev
1
New contributor
edev is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
$endgroup$
2
$begingroup$
Welcome to Code Review! The current question title, which states your concerns about the code, is too general to be useful here. Please edit to the site standard, which is for the title to simply state the task accomplished by the code. Please see How to get the best value out of Code Review: Asking Questions for guidance on writing good question titles.
$endgroup$
– Toby Speight
13 hours ago
$begingroup$
Additionally, posting a complete procedure, not just a snippet, is very helpful and highly recommended as is describing what you're trying to accomplish instead of what you're doing (your code says what you're doing). Others may see an entirely different way of achieving the end goal that completely chucks the existing code but is significantly more efficient.
$endgroup$
– FreeMan
13 hours ago
1
$begingroup$
you don't need loops or VBA at all for this. It appears that a straight forward "IFERROR" formula would suffice.
$endgroup$
– sous2817
12 hours ago
add a comment |
$begingroup$
My code takes 3 or 4 minutes to run and I want to make it more efficient. I've attached a sample of one of many loops I am using which I think are slowing it down due to needless iterations.
Dim i As Long
For i = 5 To endrow
'does a vlookup in CAIM file
Acc = Application.Cells(i, 5)
Cname = Application.Cells(i, 4)
Acname = Application.VLookup(Acc, Sheet3.Range("D2:F299090"), 3, False)
If IsError(Acname) Then
Cells(i, 42).Value = ""
Else
Cells(i, 42).Value = Acname
End If
I have a lot of similar code to the above which obviously loops through every cell (about 15k rows and I do similar procedures for several columns). Would using arrays be much quicker, for example Dim Acc(0 To x) and loop through that way?
Also, would using error handling such as On Error Resume Next be significantly quicker than checking each variable using IsError()? I tried that when I first wrote the code but couldn't get it working properly.
Thank you in advance for any help at all
vba excel
asked 13 hours ago
edevedev
1
New contributor
edev is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
$endgroup$
My code takes 3 or 4 minutes to run and I want to make it more efficient. I've attached a sample of one of many loops I am using which I think are slowing it down due to needless iterations.
Dim i As Long
For i = 5 To endrow
'does a vlookup in CAIM file
Acc = Application.Cells(i, 5)
Cname = Application.Cells(i, 4)
Acname = Application.VLookup(Acc, Sheet3.Range("D2:F299090"), 3, False)
If IsError(Acname) Then
Cells(i, 42).Value = ""
Else
Cells(i, 42).Value = Acname
End If
I have a lot of similar code to the above which obviously loops through every cell (about 15k rows and I do similar procedures for several columns). Would using arrays be much quicker, for example Dim Acc(0 To x) and loop through that way?
Also, would using error handling such as On Error Resume Next be significantly quicker than checking each variable using IsError()? I tried that when I first wrote the code but couldn't get it working properly.
Thank you in advance for any help at all
vba excel
vba excel
asked 13 hours ago
edevedev
1
New contributor
edev is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 13 hours ago
edevedev
1
New contributor
edev is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 13 hours ago
edevedev
1
New contributor
edev is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 13 hours ago
edevedev
1
asked 13 hours ago
edevedev
1
1
New contributor
edev is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
New contributor
edev is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
edev is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
2
$begingroup$
Welcome to Code Review! The current question title, which states your concerns about the code, is too general to be useful here. Please edit to the site standard, which is for the title to simply state the task accomplished by the code. Please see How to get the best value out of Code Review: Asking Questions for guidance on writing good question titles.
$endgroup$
– Toby Speight
13 hours ago
$begingroup$
Additionally, posting a complete procedure, not just a snippet, is very helpful and highly recommended as is describing what you're trying to accomplish instead of what you're doing (your code says what you're doing). Others may see an entirely different way of achieving the end goal that completely chucks the existing code but is significantly more efficient.
$endgroup$
– FreeMan
13 hours ago
1
$begingroup$
you don't need loops or VBA at all for this. It appears that a straight forward "IFERROR" formula would suffice.
$endgroup$
– sous2817
12 hours ago
add a comment |
2
$begingroup$
Welcome to Code Review! The current question title, which states your concerns about the code, is too general to be useful here. Please edit to the site standard, which is for the title to simply state the task accomplished by the code. Please see How to get the best value out of Code Review: Asking Questions for guidance on writing good question titles.
$endgroup$
– Toby Speight
13 hours ago
$begingroup$
Additionally, posting a complete procedure, not just a snippet, is very helpful and highly recommended as is describing what you're trying to accomplish instead of what you're doing (your code says what you're doing). Others may see an entirely different way of achieving the end goal that completely chucks the existing code but is significantly more efficient.
$endgroup$
– FreeMan
13 hours ago
1
$begingroup$
you don't need loops or VBA at all for this. It appears that a straight forward "IFERROR" formula would suffice.
$endgroup$
– sous2817
12 hours ago
2
2
$begingroup$
Welcome to Code Review! The current question title, which states your concerns about the code, is too general to be useful here. Please edit to the site standard, which is for the title to simply state the task accomplished by the code. Please see How to get the best value out of Code Review: Asking Questions for guidance on writing good question titles.
$endgroup$
– Toby Speight
13 hours ago
$begingroup$
Welcome to Code Review! The current question title, which states your concerns about the code, is too general to be useful here. Please edit to the site standard, which is for the title to simply state the task accomplished by the code. Please see How to get the best value out of Code Review: Asking Questions for guidance on writing good question titles.
$endgroup$
– Toby Speight
13 hours ago
$begingroup$
Additionally, posting a complete procedure, not just a snippet, is very helpful and highly recommended as is describing what you're trying to accomplish instead of what you're doing (your code says what you're doing). Others may see an entirely different way of achieving the end goal that completely chucks the existing code but is significantly more efficient.
$endgroup$
– FreeMan
13 hours ago
$begingroup$
Additionally, posting a complete procedure, not just a snippet, is very helpful and highly recommended as is describing what you're trying to accomplish instead of what you're doing (your code says what you're doing). Others may see an entirely different way of achieving the end goal that completely chucks the existing code but is significantly more efficient.
$endgroup$
– FreeMan
13 hours ago
1
1
$begingroup$
you don't need loops or VBA at all for this. It appears that a straight forward "IFERROR" formula would suffice.
$endgroup$
– sous2817
12 hours ago
$begingroup$
you don't need loops or VBA at all for this. It appears that a straight forward "IFERROR" formula would suffice.
$endgroup$
– sous2817
12 hours ago
add a comment |
0
active
oldest
votes
Your Answer
StackExchange.ifUsing("editor", function () {
return StackExchange.using("mathjaxEditing", function () {
StackExchange.MarkdownEditor.creationCallbacks.add(function (editor, postfix) {
StackExchange.mathjaxEditing.prepareWmdForMathJax(editor, postfix, [["\$", "\$"]]);
});
});
}, "mathjax-editing");
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: "196"
};
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: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
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
});
}
});
edev is a new contributor. Be nice, and check out our Code of Conduct.
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%2fcodereview.stackexchange.com%2fquestions%2f214548%2ffaster-alternative-to-multiple-loops-in-vba%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
edev is a new contributor. Be nice, and check out our Code of Conduct.
edev is a new contributor. Be nice, and check out our Code of Conduct.
edev is a new contributor. Be nice, and check out our Code of Conduct.
edev is a new contributor. Be nice, and check out our Code of Conduct.
Thanks for contributing an answer to Code Review Stack Exchange!
- 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.
Use MathJax to format equations. MathJax reference.
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%2fcodereview.stackexchange.com%2fquestions%2f214548%2ffaster-alternative-to-multiple-loops-in-vba%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
2
$begingroup$
Welcome to Code Review! The current question title, which states your concerns about the code, is too general to be useful here. Please edit to the site standard, which is for the title to simply state the task accomplished by the code. Please see How to get the best value out of Code Review: Asking Questions for guidance on writing good question titles.
$endgroup$
– Toby Speight
13 hours ago
$begingroup$
Additionally, posting a complete procedure, not just a snippet, is very helpful and highly recommended as is describing what you're trying to accomplish instead of what you're doing (your code says what you're doing). Others may see an entirely different way of achieving the end goal that completely chucks the existing code but is significantly more efficient.
$endgroup$
– FreeMan
13 hours ago
1
$begingroup$
you don't need loops or VBA at all for this. It appears that a straight forward "IFERROR" formula would suffice.
$endgroup$
– sous2817
12 hours ago