Worksheetfunction.Vlookup value on error The Next CEO of Stack OverflowRevisited IsTypeSafe...
What benefits would be gained by using human laborers instead of drones in deep sea mining?
Is it my responsibility to learn a new technology in my own time my employer wants to implement?
If Nick Fury and Coulson already knew about aliens (Kree and Skrull) why did they wait until Thor's appearance to start making weapons?
Ubuntu shell scripting
Why is the US ranked as #45 in Press Freedom ratings, despite its extremely permissive free speech laws?
How to avoid supervisors with prejudiced views?
Help understanding this unsettling image of Titan, Epimetheus, and Saturn's rings?
Is micro rebar a better way to reinforce concrete than rebar?
Would this house-rule that treats advantage as a +1 to the roll instead (and disadvantage as -1) and allows them to stack be balanced?
How to scale a tikZ image which is within a figure environment
Is French Guiana a (hard) EU border?
Do I need to write [sic] when a number is less than 10 but isn't written out?
Why is quantifier elimination desirable for a given theory?
Is it professional to write unrelated content in an almost-empty email?
How does Madhvacharya interpret Bhagavad Gita sloka 18.66?
What does "Its cash flow is deeply negative" mean?
Display a text message if the shortcode is not found?
How to count occurrences of text in a file?
Why does the flight controls check come before arming the autobrake on the A320?
Is this "being" usage is essential?
How to get from Geneva Airport to Metabief?
I want to make a picture in physics with TikZ. Can you help me?
What did we know about the Kessel run before the prologues?
How to place nodes around a circle from some initial angle?
Worksheetfunction.Vlookup value on error
The Next CEO of Stack OverflowRevisited IsTypeSafe method implementation for “type-safe” ListStandard Methods in VBAFunction to return a string legal for a range nameFunction to return a legal name for an Excel range: Follow-upEnd of day receipt emailing processFunctional FrameworkClass to retrieve external dataA Moses's worthy string splitterGeneral function to test for empty/no-value controlsExposing LocalDB API to VBA
$begingroup$
I have this function (wb is a public workbook variable):
Function GetTiersAccount(TiersName As String, Clients As Boolean) As Long
If Clients = True Then
On Error GoTo ERRRRR
GetTiersAccount = WorksheetFunction.VLookup(TiersName, wb.Sheets(1).Range("A1:B13"), 2, False)
Exit Function
Else
On Error GoTo ERRRRR
GetTiersAccount = WorksheetFunction.VLookup(TiersName, wb.Sheets(1).Range("D1:E94"), 2, False)
Exit Function
End If
ERRRRR:
GetTiersAccount = 471
End Function
I feel like the way I have managed to deal with worksheetfunction.vlookup error unelegant. However I want Gettieraccount to return 471 on vlookup error.
There must be a better way to do that.
Thanks in advance.
vba
asked 2 days ago
a1a1a1a1a1a1a1a1a1a1
296
$endgroup$
add a comment |
$begingroup$
I have this function (wb is a public workbook variable):
Function GetTiersAccount(TiersName As String, Clients As Boolean) As Long
If Clients = True Then
On Error GoTo ERRRRR
GetTiersAccount = WorksheetFunction.VLookup(TiersName, wb.Sheets(1).Range("A1:B13"), 2, False)
Exit Function
Else
On Error GoTo ERRRRR
GetTiersAccount = WorksheetFunction.VLookup(TiersName, wb.Sheets(1).Range("D1:E94"), 2, False)
Exit Function
End If
ERRRRR:
GetTiersAccount = 471
End Function
I feel like the way I have managed to deal with worksheetfunction.vlookup error unelegant. However I want Gettieraccount to return 471 on vlookup error.
There must be a better way to do that.
Thanks in advance.
vba
asked 2 days ago
a1a1a1a1a1a1a1a1a1a1
296
$endgroup$
$begingroup$
How about stackoverflow.com/a/18064104
$endgroup$
– Heslacher
2 days ago
add a comment |
$begingroup$
I have this function (wb is a public workbook variable):
Function GetTiersAccount(TiersName As String, Clients As Boolean) As Long
If Clients = True Then
On Error GoTo ERRRRR
GetTiersAccount = WorksheetFunction.VLookup(TiersName, wb.Sheets(1).Range("A1:B13"), 2, False)
Exit Function
Else
On Error GoTo ERRRRR
GetTiersAccount = WorksheetFunction.VLookup(TiersName, wb.Sheets(1).Range("D1:E94"), 2, False)
Exit Function
End If
ERRRRR:
GetTiersAccount = 471
End Function
I feel like the way I have managed to deal with worksheetfunction.vlookup error unelegant. However I want Gettieraccount to return 471 on vlookup error.
There must be a better way to do that.
Thanks in advance.
vba
asked 2 days ago
a1a1a1a1a1a1a1a1a1a1
296
$endgroup$
I have this function (wb is a public workbook variable):
Function GetTiersAccount(TiersName As String, Clients As Boolean) As Long
If Clients = True Then
On Error GoTo ERRRRR
GetTiersAccount = WorksheetFunction.VLookup(TiersName, wb.Sheets(1).Range("A1:B13"), 2, False)
Exit Function
Else
On Error GoTo ERRRRR
GetTiersAccount = WorksheetFunction.VLookup(TiersName, wb.Sheets(1).Range("D1:E94"), 2, False)
Exit Function
End If
ERRRRR:
GetTiersAccount = 471
End Function
I feel like the way I have managed to deal with worksheetfunction.vlookup error unelegant. However I want Gettieraccount to return 471 on vlookup error.
There must be a better way to do that.
Thanks in advance.
vba
vba
asked 2 days ago
a1a1a1a1a1a1a1a1a1a1
296
asked 2 days ago
a1a1a1a1a1a1a1a1a1a1
296
asked 2 days ago
a1a1a1a1a1a1a1a1a1a1
296
asked 2 days ago
a1a1a1a1a1a1a1a1a1a1
296
asked 2 days ago
a1a1a1a1a1a1a1a1a1a1
296
296
$begingroup$
How about stackoverflow.com/a/18064104
$endgroup$
– Heslacher
2 days ago
add a comment |
$begingroup$
How about stackoverflow.com/a/18064104
$endgroup$
– Heslacher
2 days ago
$begingroup$
How about stackoverflow.com/a/18064104
$endgroup$
– Heslacher
2 days ago
$begingroup$
How about stackoverflow.com/a/18064104
$endgroup$
– Heslacher
2 days ago
add a comment |
1 Answer
1
active
oldest
votes
$begingroup$
Your handling is not necessarily unelegant, but there are a couple of things to point out.
The main thing is that you're repeating code that really only needs to be there once. For example, you only need to write the VLookup statement once. Just set up a variable to define your range.
Your error handling can work, but you need to more clearly define what is a "normal exit" from your routine and what is an error exit. It's typical in this case to structure your error handling with the form shown below because there may be processing required during a normal exit only that you still might have to perform during an error exit.
Option Explicit
Function GetTiersAccount(TiersName As String, Clients As Boolean) As Long
Dim lookupRange As Range
If Clients = True Then
Set lookupRange = wb.Sheets(1).Range("A1:B13")
Else
Set lookupRange = wb.Sheets(1).Range("D1:E94")
End If
On Error GoTo ERRRRR
GetTiersAccount = WorksheetFunction.VLookup(TiersName, lookupRange, 2, False)
NormalExit:
Exit Function
ERRRRR:
GetTiersAccount = 471
Resume NormalExit
End Function
answered yesterday
PeterTPeterT
1,341211
$endgroup$
add a comment |
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
});
}
});
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%2f216428%2fworksheetfunction-vlookup-value-on-error%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
$begingroup$
Your handling is not necessarily unelegant, but there are a couple of things to point out.
The main thing is that you're repeating code that really only needs to be there once. For example, you only need to write the VLookup statement once. Just set up a variable to define your range.
Your error handling can work, but you need to more clearly define what is a "normal exit" from your routine and what is an error exit. It's typical in this case to structure your error handling with the form shown below because there may be processing required during a normal exit only that you still might have to perform during an error exit.
Option Explicit
Function GetTiersAccount(TiersName As String, Clients As Boolean) As Long
Dim lookupRange As Range
If Clients = True Then
Set lookupRange = wb.Sheets(1).Range("A1:B13")
Else
Set lookupRange = wb.Sheets(1).Range("D1:E94")
End If
On Error GoTo ERRRRR
GetTiersAccount = WorksheetFunction.VLookup(TiersName, lookupRange, 2, False)
NormalExit:
Exit Function
ERRRRR:
GetTiersAccount = 471
Resume NormalExit
End Function
answered yesterday
PeterTPeterT
1,341211
$endgroup$
add a comment |
$begingroup$
Your handling is not necessarily unelegant, but there are a couple of things to point out.
The main thing is that you're repeating code that really only needs to be there once. For example, you only need to write the VLookup statement once. Just set up a variable to define your range.
Your error handling can work, but you need to more clearly define what is a "normal exit" from your routine and what is an error exit. It's typical in this case to structure your error handling with the form shown below because there may be processing required during a normal exit only that you still might have to perform during an error exit.
Option Explicit
Function GetTiersAccount(TiersName As String, Clients As Boolean) As Long
Dim lookupRange As Range
If Clients = True Then
Set lookupRange = wb.Sheets(1).Range("A1:B13")
Else
Set lookupRange = wb.Sheets(1).Range("D1:E94")
End If
On Error GoTo ERRRRR
GetTiersAccount = WorksheetFunction.VLookup(TiersName, lookupRange, 2, False)
NormalExit:
Exit Function
ERRRRR:
GetTiersAccount = 471
Resume NormalExit
End Function
answered yesterday
PeterTPeterT
1,341211
$endgroup$
add a comment |
$begingroup$
Your handling is not necessarily unelegant, but there are a couple of things to point out.
The main thing is that you're repeating code that really only needs to be there once. For example, you only need to write the VLookup statement once. Just set up a variable to define your range.
Your error handling can work, but you need to more clearly define what is a "normal exit" from your routine and what is an error exit. It's typical in this case to structure your error handling with the form shown below because there may be processing required during a normal exit only that you still might have to perform during an error exit.
Option Explicit
Function GetTiersAccount(TiersName As String, Clients As Boolean) As Long
Dim lookupRange As Range
If Clients = True Then
Set lookupRange = wb.Sheets(1).Range("A1:B13")
Else
Set lookupRange = wb.Sheets(1).Range("D1:E94")
End If
On Error GoTo ERRRRR
GetTiersAccount = WorksheetFunction.VLookup(TiersName, lookupRange, 2, False)
NormalExit:
Exit Function
ERRRRR:
GetTiersAccount = 471
Resume NormalExit
End Function
answered yesterday
PeterTPeterT
1,341211
$endgroup$
Your handling is not necessarily unelegant, but there are a couple of things to point out.
The main thing is that you're repeating code that really only needs to be there once. For example, you only need to write the VLookup statement once. Just set up a variable to define your range.
Your error handling can work, but you need to more clearly define what is a "normal exit" from your routine and what is an error exit. It's typical in this case to structure your error handling with the form shown below because there may be processing required during a normal exit only that you still might have to perform during an error exit.
Option Explicit
Function GetTiersAccount(TiersName As String, Clients As Boolean) As Long
Dim lookupRange As Range
If Clients = True Then
Set lookupRange = wb.Sheets(1).Range("A1:B13")
Else
Set lookupRange = wb.Sheets(1).Range("D1:E94")
End If
On Error GoTo ERRRRR
GetTiersAccount = WorksheetFunction.VLookup(TiersName, lookupRange, 2, False)
NormalExit:
Exit Function
ERRRRR:
GetTiersAccount = 471
Resume NormalExit
End Function
answered yesterday
PeterTPeterT
1,341211
answered yesterday
PeterTPeterT
1,341211
answered yesterday
PeterTPeterT
1,341211
answered yesterday
PeterTPeterT
1,341211
1,341211
add a comment |
add a comment |
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%2f216428%2fworksheetfunction-vlookup-value-on-error%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
$begingroup$
How about stackoverflow.com/a/18064104
$endgroup$
– Heslacher
2 days ago