VLookup with InputBox 1

[stillwillyboy]

I am wanting to use VLookup during the process of data entry to verify that a record exists. I have tried the following but it always goes to the MsgBox. I have verified that the client number that I am entering does exist on the sheet ClntInfo in the named range ClientInfoAll. What am I missing. Thanks, Bill


If Len(ClientEntry) = 4 And IsNumeric(ClientEntry) _
And ClientEntry >= 1000 And ClientEntry <= 2999 And _
ClientEntry = "=VLookup(ClientEntry, clntinfo!clientinfoall, 1, False)" Then
ValidEntry1 = True
Range("A65536").End(xlUp).Offset(1, 0) = ClientEntry
Else
MsgBox "Invalid Client Number. Either not 4 digits, " _
& "not in range between 1000 and 2999 or no record for this client."

ClientEntry = ""
End If
End If

 

[rmikesmith]

Hi Bill,

In short, the offending portion of your code is:

ClientEntry = "=VLookup(ClientEntry, clntinfo!clientinfoall, 1, False)"

The user is entering a 4-digit string but you are comparing it, not to the result of using the VLookup function, but rather the literal string =VLookup(ClientEntry, clntinfo!clientinfoall, 1, False) In other words, the above line of code does not invoke the VLookup function but simply compares one string to another.
To my mind, there are other issues as well. Primarily, trying to combine too many disparate conditions in the If..Then. It would be better to test these separately then give the user specific feedback.

One other question: Assuming the VLookup did work as written, and the user-input ClientEntry is found, why enter this as a new line of the client data region (e.g., Range("A65536").End(xlUp).Offset(1, 0) = ClientEntry)?

I can provide some revised code after I understand what it's supposed to do.

Regards,
Mike

 

[stillwillyboy]

Mike,

I am wanting to check the existence of the Client Number during data entry. If the number does not exist in the “ClientInfo” sheet, tell the data entry person immediately so they can correct the mistake or add the record to ClientInfo if need be.

Currently, the way I have my program written, it will accept the number onto the sheet (“TimeSheetEntry”) even if the number is not valid (i.e. not set up on the “ClientInfo” sheet). My current program does not discover the non-existence of the client record until it processes the info. This is not good data entry or programming on my part.

The line: Range("A65536").End(xlUp).Offset(1, 0) = ClientEntry) is to add the client number entry (new record) to the end of the sheet “TimeSheetEntry”.

I have the same issue with entering job information as above, but I figure if I can get the Client taken care of, the job info will be very similar.

I agree with the number of If..Then statements, but wasn’t sure of the best way to do it. The way I have it works, except for the VLookup, but I know it could be better.

Thanks,

Bill

 

[rmikesmith]

Hi Bill,

I see now. The missing bit of information was that the line:

Range("A65536").End(xlUp).Offset(1, 0) = ClientEntry)

implicitly referenced the TimeSheetEntry worksheet. It is better to include an explicit reference.
Here is a code example I put together yesterday. I have added an explicit worksheet reference based on your response:

Dim Result As Variant
Dim UserInput As Variant
Dim ClientNum As String
Dim ValidEntry1 As Boolean

  ClientNum = Application.InputBox(Prompt:="Enter Client Number:", Type:=1)
  If ClientNum = False Then Exit Sub
  
  If Len(ClientNum) <> 4 Then
    MsgBox "Client Number must be 4 digits"
    Exit Sub
  ElseIf ClientNum < 1000 Or ClientNum > 2999 Then
    MsgBox "Client Number must be between 1000 and 299"
    Exit Sub
  End If
  
  Result = Application.VLookup(ClientNum, Worksheets("clntinfo").Range("ClientInfoAll"), 1, False)
  
  If Not IsError(Result) Then
    ValidEntry1 = True
    [b]Worksheets("TimeSheetEntry").[/b]Range("A65536").End(xlUp).Offset(1, 0) = ClientNum
  Else
    MsgBox "There is no record for this client."
    ClientNum = ""
  End If

Note: I have separated out the individual tests for the user-supplied ClientNum (BTW, this replaces your ClientEntry) to provide specific feedback to the user. Also, by using the Excel's InputBox instead of VBA's you can specify a numeric input (with built-in notification to the user if it's not) and you can test explicitly if the user selected Cancel.

Regards,
Mike

 

[stillwillyboy]

Mike,

The line: Result = Application.VLookup(ClientNum, Worksheets("clntinfo").Range("ClientInfoAll"), 1, False) is not working.

When I step thru the proc (using F8), the variable “Result” ends up with “Result = Error 2042”. When stepping thru, the proc skips the lines of ValidEntry1 = True as well as Worksheets("TimeSheetEntry").Range("A65536").End(xlUp).Offset(1, 0) = ClientNum. It goes to the “no record” message.

I looked at the FAQ here on Tek-Tips and it appears there is a bit of a bug with VLookup when using Application.VLookup as well as WorksheetFunction.VLookup. I have verified the existence of the client number in CLNTINFO. The other parts of the proc work. Should I ditch the VLookup?

Bill

 

[rmikesmith]

Bill,

There is no problem with VLookup. Error 2042 is returned when the searched for value is not found in the search range (ClientInfoAll, in this case). However, the user-input value of ClientNum must be exactly the same as it exists on the sheet ClntInfo. This includes extraneous spaces. I obviously didn't have your worksheet to work from, but did test the supplied code on some mock data and it worked as expected: Setting ValidEntry1 = True and adding ClientNum to the TimeSheetEntry worksheet when ClientNum exists on ClntInfo or displaying the invalid entry message to the user when it doesn't. Check your data on ClntInfo for extra spaces or other anomalies.


Regards,
Mike

 

[rmikesmith]

Bill,

Here is an alternative, since you are not really using VLookup in a typical fashion (i.e., returning a secondary column's data from the same row as that of a key column's lookup value); instead, you are simply trying to determine the presence/absence of a value in the list. The following uses the Find method of the Range object (key changes are bolded):

[b]Dim ResultRng As Range[/b]
Dim ClientNum As String
Dim ValidEntry1 As Boolean

  ClientNum = Application.InputBox(Prompt:="Enter Client Number:", Type:=1)
  If ClientNum = False Then Exit Sub
  
  If Len(ClientNum) <> 4 Then
    MsgBox "Client Number must be 4 digits"
    Exit Sub
  ElseIf ClientNum < 1000 Or ClientNum > 2999 Then
    MsgBox "Client Number must be between 1000 and 299"
    Exit Sub
  End If
  
  [b]Set ResultRng = Worksheets("clntinfo").Range("ClientInfoAll").Find(What:=ClientNum, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows)[/b]
  
  [b]If Not ResultRng Is Nothing Then[/b]
    ValidEntry1 = True
    Worksheets("TimeSheetEntry").Range("A65536").End(xlUp).Offset(1, 0) = ClientNum
  Else
    MsgBox "There is no record for this client."
    ClientNum = ""
  End If

If there is still a problem with this "finding" a client number you know to be present, try changing the LookAt parameter to xlPart.


Regards,
Mike

 

[stillwillyboy]

Awesome! Thanks. I think I can take this and then use it for the Job info also needed during data entry.

Thanks!