• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VLookup with IfError to create a new record

F

FranktheBank

Member
Hello all,

Many 'Thanks' in advance.

I'm trying to write some script that if the user enters an 'unused' position number in the Userform, certain actions will occur (right now I just want it to say "Caution: This is an unused Position number The information you provide will be the default for this position from this point forward."

I'm getting the message: Compile Error: Argument no optional and it has a blue highlight over the '.VLookup'

Code: 
Private Sub New_Position_Number_AfterUpdate()
New_Position_Number.Value = Format(New_Position_Number.Value, "00000000")
WorksheetFunction.IfError(WorksheetFunction.VLookup(CDbl(New_Position_Number.Value), Sheets("Sheet4").Range("a2:h250")), 1, False, "Caution: This is an unused Position number The information you provide will be the default for this position from this point forward.") = ""
Position_Title.Value = WorksheetFunction.VLookup(CDbl(New_Position_Number.Value), Sheets("Sheet4").Range("a2:h250"), 2, False)
Pos_Class.Value = WorksheetFunction.VLookup(CDbl(New_Position_Number.Value), Sheets("Sheet4").Range("a2:h250"), 3, False)
End Sub

I've never used the IfError function before
 
You have brackets in wrong position. Taking just IfError part. It should look like below.
Code: 
WorksheetFunction.IfError(WorksheetFunction.VLookup(CDbl(New_Position_Number.Value), Sheets("Sheet4").Range("a2:h250"), 1, False), "Caution: This is an unused Position number The information you provide will be the default for this position from this point forward.")
 
Okay Thanks, but now I have:
Code: 
Private Sub New_Position_Number_AfterUpdate()
New_Position_Number.Value = Format(New_Position_Number.Value, "00000000")
WorksheetFunction.IfError(WorksheetFunction.VLookup(CDbl(New_Position_Number.Value), Sheets("Sheet4").Range("a2:h250")), 1, False, "Caution: This is an unused Position number The information you provide will be the default for this position from this point forward.")
Position_Title.Value = WorksheetFunction.VLookup(CDbl(New_Position_Number.Value), Sheets("Sheet4").Range("a2:h250"), 2, False)
Pos_Class.Value = WorksheetFunction.VLookup(CDbl(New_Position_Number.Value), Sheets("Sheet4").Range("a2:h250"), 3, False)

and I'm getting the Error Code Compile Error: Syntax error

and the line:
WorksheetFunction.IfError(WorksheetFunction.VLookup(CDbl(New_Position_Number.Value), Sheets("Sheet4").Range("a2:h250")), 1, False, "Caution: This is an unused Position number The information you provide will be the default for this position from this point forward.")
Click to expand...

is in red
 
Right, since Vlookup needs following arguments (Range_Lookup being optional).
VLOOKUP(LookupValue, TableArray, Col_Index, [Range_Lookup])

And IFERROR only accepts 2 arguments.
=IFERROR(Value,Value_If_Error)

Therefore your syntax for both IFERROR and VLOOKUP is off.

If you look at your code. You are closing off VLOOKUP at TableArray and not at [Range_Lookup]. Thus VLOOKUP has too few argument and IFERROR with too many.

Code: 
WorksheetFunction.IfError(WorksheetFunction.VLookup(CDbl(New_Position_Number.Value), Sheets("Sheet4").Range("a2:h250")), 1, False, "Caution: This is an unused Position number The information you provide will be the default for this position from this point forward.")

Change it like I wrote in previous post and see what happens.
Code: 
WorksheetFunction.IfError(WorksheetFunction.VLookup(CDbl(New_Position_Number.Value), Sheets("Sheet4").Range("a2:h250"), 1, False), "Caution: This is an unused Position number The information you provide will be the default for this position from this point forward.")
 
Arghh.

I copied and pasted your line (just to make sure no typos)

I'm still getting the same Compile error: Syntax Error

I also tried an existing number and I get the same error, so there is something wrong with the argument?? When I scroll over it should I not at least be getting the number I chose when I scroll over the part that says:(New_Position_Number.value)??
 
Well another thing I noticed that your IFERROR line isn't set to = and variable...

Other than that, without seeing your entire setup (workbook) hard to pinpoint the issue.
 
O.K. back-up a bit, you say "your IFERROR line isn't set to = and variable..."

What exactly do you mean?? Should I have some VBA after this point? What exactly for example?? Should I have the IfError "False' then do the following??
 
Normally when you do calculation you need to cast the result to some variable.

Ex:
Code: 
Dim CalcResult as Variant

CalcResult = WorksheetFunction.IfError(WorksheetFunction.VLookup(CDbl(New_Position_Number.Value), Sheets("Sheet4").Range("a2:h250"), 1, False), "Caution: This is an unused Position number The information you provide will be the default for this position from this point forward.")
 
Okay so basically I'm using the wrong function!!

I just wanted the macro to take the 'Input' "New_Position_Number" and if it was already being used then do one thing and if it wasn't being used do something else.

Therefor I could probably use:
if(vlookup(New_Position_Number,Sheets("Sheet4").Range("a2:h250"), 1, False), "Whatever happens with an existing Position", "Whatever happens when New_position_Number is not found")

Thanks for your patience
 
Why not use IF statement in VBA instead of using Worksheetfunction?

Something like...
Code: 
IF CalcResult = TRUE Then
  'Do some operation
Else
  'Do some other operation
End If
 
Thanks for your help.

I've been using the 'Worksheetfunction' because earlier I tried just using vlookup and it didn't work and I read somehwere to use Worksheetfunction.vlookup.

Not really sure why it didn't work before??
 
You must log in or register to reply here.
Back
Top