Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Discussion (Misc queries) > Vlookup Problem
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 30
Default Vlookup Problem
I have a userform with textbox3. The user enters a value into textbox3.
When the user moves to the next userform control, I want VBA to check the
value entered in textbox3 against a table I have on a worksheet (CE Tables).
If the value exists in the table, the focus will move to the next control,
but if the value doesn't exist, I want a msgbox to give a warning, then clear
the value and return to textbox3. Currently, I get a "Type Mismatch" with
MsgBox varDN.Value.

Private Sub TextBox3_Exit(ByVal Cancel As MSForms.ReturnBoolean)

Dim varDN As Variant

varDN = Application.VLookup(TextBox3.Value, Worksheets("CE
Tables").Range("O:O"), 1, False)
MsgBox varDN.Value

If IsError(varDN) = True And TextBox3.Value < "" Then
MsgBox "Non-Standard DN Value"
TextBox3.Value = ""
Textbox3.SetFocus
End If

End Sub
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 1,805
Default Vlookup Problem
You will get mismatch error if Vlookup does not find a match as it will then
return #N/A...

Use
If IsError(varDN) = True Then
MsgBox TextBox3.Value & " not found"
Else
MsgBox varDN
End If

In fact you do not need this message as you have already covered it in the
next IF statement...
"aftamath77" wrote:

I have a userform with textbox3. The user enters a value into textbox3.
When the user moves to the next userform control, I want VBA to check the
value entered in textbox3 against a table I have on a worksheet (CE Tables).
If the value exists in the table, the focus will move to the next control,
but if the value doesn't exist, I want a msgbox to give a warning, then clear
the value and return to textbox3. Currently, I get a "Type Mismatch" with
MsgBox varDN.Value.

Private Sub TextBox3_Exit(ByVal Cancel As MSForms.ReturnBoolean)

Dim varDN As Variant

varDN = Application.VLookup(TextBox3.Value, Worksheets("CE
Tables").Range("O:O"), 1, False)
MsgBox varDN.Value

If IsError(varDN) = True And TextBox3.Value < "" Then
MsgBox "Non-Standard DN Value"
TextBox3.Value = ""
Textbox3.SetFocus
End If

End Sub
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 11,501
Default Vlookup Problem
Hi,

Try this. I've left setfocus in but doubt it will work AFAIK it's vey buggy.
so you'll have to re-select manually.

Private Sub TextBox3_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim varDN As Variant
If TextBox3.Value < "" Then
varDN = Application.VLookup(TextBox3.Value,
Worksheets("CETables").Range("O:O"), 1, False)
End If
If IsError(varDN) And TextBox3.Value < "" Then
MsgBox "Non-Standard DN Value"
TextBox3.Value = ""
TextBox3.SetFocus
End If
End Sub

Mike
"aftamath77" wrote:

I have a userform with textbox3. The user enters a value into textbox3.
When the user moves to the next userform control, I want VBA to check the
value entered in textbox3 against a table I have on a worksheet (CE Tables).
If the value exists in the table, the focus will move to the next control,
but if the value doesn't exist, I want a msgbox to give a warning, then clear
the value and return to textbox3. Currently, I get a "Type Mismatch" with
MsgBox varDN.Value.

Private Sub TextBox3_Exit(ByVal Cancel As MSForms.ReturnBoolean)

Dim varDN As Variant

varDN = Application.VLookup(TextBox3.Value, Worksheets("CE
Tables").Range("O:O"), 1, False)
MsgBox varDN.Value

If IsError(varDN) = True And TextBox3.Value < "" Then
MsgBox "Non-Standard DN Value"
TextBox3.Value = ""
Textbox3.SetFocus
End If

End Sub
Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 30
Default Vlookup Problem
I receive another error, "Subscript out of range," on the code line: varDN =
Application.VLookup(TextBox3.Value, Worksheets("CETables").Range("O:O"), 1,
False)

"Mike H" wrote:

Hi,

Try this. I've left setfocus in but doubt it will work AFAIK it's vey buggy.
so you'll have to re-select manually.

Private Sub TextBox3_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim varDN As Variant
If TextBox3.Value < "" Then
varDN = Application.VLookup(TextBox3.Value,
Worksheets("CETables").Range("O:O"), 1, False)
End If
If IsError(varDN) And TextBox3.Value < "" Then
MsgBox "Non-Standard DN Value"
TextBox3.Value = ""
TextBox3.SetFocus
End If
End Sub

Mike
"aftamath77" wrote:

I have a userform with textbox3. The user enters a value into textbox3.
When the user moves to the next userform control, I want VBA to check the
value entered in textbox3 against a table I have on a worksheet (CE Tables).
If the value exists in the table, the focus will move to the next control,
but if the value doesn't exist, I want a msgbox to give a warning, then clear
the value and return to textbox3. Currently, I get a "Type Mismatch" with
MsgBox varDN.Value.

Private Sub TextBox3_Exit(ByVal Cancel As MSForms.ReturnBoolean)

Dim varDN As Variant

varDN = Application.VLookup(TextBox3.Value, Worksheets("CE
Tables").Range("O:O"), 1, False)
MsgBox varDN.Value

If IsError(varDN) = True And TextBox3.Value < "" Then
MsgBox "Non-Standard DN Value"
TextBox3.Value = ""
Textbox3.SetFocus
End If

End Sub
Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 30
Default Vlookup Problem
My mistake, I forgot to put the space between CE and Tables. But now I get
the message box, even though I input a number that I know exists in the table.

"aftamath77" wrote:

I receive another error, "Subscript out of range," on the code line: varDN =
Application.VLookup(TextBox3.Value, Worksheets("CETables").Range("O:O"), 1,
False)

"Mike H" wrote:

Hi,

Try this. I've left setfocus in but doubt it will work AFAIK it's vey buggy.
so you'll have to re-select manually.

Private Sub TextBox3_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim varDN As Variant
If TextBox3.Value < "" Then
varDN = Application.VLookup(TextBox3.Value,
Worksheets("CETables").Range("O:O"), 1, False)
End If
If IsError(varDN) And TextBox3.Value < "" Then
MsgBox "Non-Standard DN Value"
TextBox3.Value = ""
TextBox3.SetFocus
End If
End Sub

Mike
"aftamath77" wrote:

I have a userform with textbox3. The user enters a value into textbox3.
When the user moves to the next userform control, I want VBA to check the
value entered in textbox3 against a table I have on a worksheet (CE Tables).
If the value exists in the table, the focus will move to the next control,
but if the value doesn't exist, I want a msgbox to give a warning, then clear
the value and return to textbox3. Currently, I get a "Type Mismatch" with
MsgBox varDN.Value.

Private Sub TextBox3_Exit(ByVal Cancel As MSForms.ReturnBoolean)

Dim varDN As Variant

varDN = Application.VLookup(TextBox3.Value, Worksheets("CE
Tables").Range("O:O"), 1, False)
MsgBox varDN.Value

If IsError(varDN) = True And TextBox3.Value < "" Then
MsgBox "Non-Standard DN Value"
TextBox3.Value = ""
Textbox3.SetFocus
End If

End Sub
Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 11,501
Default Vlookup Problem
If that's happening the error is in the data because it works perfectly for
me. the usual suspects are rougue spaces in the O:O range

Mike

"aftamath77" wrote:

My mistake, I forgot to put the space between CE and Tables. But now I get
the message box, even though I input a number that I know exists in the table.

"aftamath77" wrote:

I receive another error, "Subscript out of range," on the code line: varDN =
Application.VLookup(TextBox3.Value, Worksheets("CETables").Range("O:O"), 1,
False)

"Mike H" wrote:

Hi,

Try this. I've left setfocus in but doubt it will work AFAIK it's vey buggy.
so you'll have to re-select manually.

Private Sub TextBox3_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim varDN As Variant
If TextBox3.Value < "" Then
varDN = Application.VLookup(TextBox3.Value,
Worksheets("CETables").Range("O:O"), 1, False)
End If
If IsError(varDN) And TextBox3.Value < "" Then
MsgBox "Non-Standard DN Value"
TextBox3.Value = ""
TextBox3.SetFocus
End If
End Sub

Mike
"aftamath77" wrote:

I have a userform with textbox3. The user enters a value into textbox3.
When the user moves to the next userform control, I want VBA to check the
value entered in textbox3 against a table I have on a worksheet (CE Tables).
If the value exists in the table, the focus will move to the next control,
but if the value doesn't exist, I want a msgbox to give a warning, then clear
the value and return to textbox3. Currently, I get a "Type Mismatch" with
MsgBox varDN.Value.

Private Sub TextBox3_Exit(ByVal Cancel As MSForms.ReturnBoolean)

Dim varDN As Variant

varDN = Application.VLookup(TextBox3.Value, Worksheets("CE
Tables").Range("O:O"), 1, False)
MsgBox varDN.Value

If IsError(varDN) = True And TextBox3.Value < "" Then
MsgBox "Non-Standard DN Value"
TextBox3.Value = ""
Textbox3.SetFocus
End If

End Sub
Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 35,218
Default Vlookup Problem
Just some suggestions...

First, since you're looking to see if the value is in a column, you could use
application.match() instead of =vlookup() (and returning 1).

Second, instead of showing a msgbox for errors, you could add a label to the
userform. Then the user can read your error message there and not have to
dismiss any dialog box.

Third, varDN won't have a .value property.

If you use application.vlookup(), it'll match the textbox3 value (if there is a
match) or an error. You could just use: msgbox varDN

If you application.match(), it'll return a number (the row index of the lookup
range, if there is a match) or an error. You could use: msgbox varDN

In fact, you'd want to check to see if there's an error:

if iserror(vardn) then
msgbox "no match"
else
msgbox vardn
end if

if vardn is an error, then this: msgbox vardn
won't work at all.


Option Explicit
Private Sub CommandButton2_Click()
Unload Me
End Sub
Private Sub TextBox3_Exit(ByVal Cancel As MSForms.ReturnBoolean)

Dim varDN As Variant

Me.Label1.Caption = ""

If Me.TextBox3.Value = "" Then
Me.Label1.Caption = "DN Entry is blank!"
Cancel = True
End If

'textbox3.value is text--not a number
'so if column O can contain either text or numbers
'we can look twice.
'if it only contains numbers, you could drop the first
'application.match()
'And if you only have integers, you can use
'clng() instead of cdbl().
varDN = Application.Match(TextBox3.Value, _
Worksheets("CE Tables").Range("O:O"), 0)
If IsError(varDN) Then
If IsNumeric(Me.TextBox3.Value) Then
varDN = Application.Match(CDbl(TextBox3.Value), _
Worksheets("CE Tables").Range("O:O"), 0)
End If
End If

If IsError(varDN) Then
Me.Label1.Caption = "Non-Standard DN Value"
Me.TextBox3.Value = ""
Cancel = True
End If

End Sub
Private Sub UserForm_Initialize()
With Me.CommandButton1
.Caption = "Ok"
.Default = True
End With
With Me.CommandButton2
.Caption = "Cancel"
.Cancel = True
'allow you to click cancel if you're in textbox3.
.TakeFocusOnClick = False
End With
With Me.Label1
.Caption = ""
.ForeColor = vbRed
End With
End Sub


aftamath77 wrote:

I have a userform with textbox3. The user enters a value into textbox3.
When the user moves to the next userform control, I want VBA to check the
value entered in textbox3 against a table I have on a worksheet (CE Tables).
If the value exists in the table, the focus will move to the next control,
but if the value doesn't exist, I want a msgbox to give a warning, then clear
the value and return to textbox3. Currently, I get a "Type Mismatch" with
MsgBox varDN.Value.

Private Sub TextBox3_Exit(ByVal Cancel As MSForms.ReturnBoolean)

Dim varDN As Variant

varDN = Application.VLookup(TextBox3.Value, Worksheets("CE
Tables").Range("O:O"), 1, False)
MsgBox varDN.Value

If IsError(varDN) = True And TextBox3.Value < "" Then
MsgBox "Non-Standard DN Value"
TextBox3.Value = ""
Textbox3.SetFocus
End If

End Sub

--

Dave Peterson
Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 30
Default Vlookup Problem
Thanks Dave, these were all great suggestions. I used the .Match method.
Keep up the great work you do on this site.

"Dave Peterson" wrote:

Just some suggestions...

First, since you're looking to see if the value is in a column, you could use
application.match() instead of =vlookup() (and returning 1).

Second, instead of showing a msgbox for errors, you could add a label to the
userform. Then the user can read your error message there and not have to
dismiss any dialog box.

Third, varDN won't have a .value property.

If you use application.vlookup(), it'll match the textbox3 value (if there is a
match) or an error. You could just use: msgbox varDN

If you application.match(), it'll return a number (the row index of the lookup
range, if there is a match) or an error. You could use: msgbox varDN

In fact, you'd want to check to see if there's an error:

if iserror(vardn) then
msgbox "no match"
else
msgbox vardn
end if

if vardn is an error, then this: msgbox vardn
won't work at all.


Option Explicit
Private Sub CommandButton2_Click()
Unload Me
End Sub
Private Sub TextBox3_Exit(ByVal Cancel As MSForms.ReturnBoolean)

Dim varDN As Variant

Me.Label1.Caption = ""

If Me.TextBox3.Value = "" Then
Me.Label1.Caption = "DN Entry is blank!"
Cancel = True
End If

'textbox3.value is text--not a number
'so if column O can contain either text or numbers
'we can look twice.
'if it only contains numbers, you could drop the first
'application.match()
'And if you only have integers, you can use
'clng() instead of cdbl().
varDN = Application.Match(TextBox3.Value, _
Worksheets("CE Tables").Range("O:O"), 0)
If IsError(varDN) Then
If IsNumeric(Me.TextBox3.Value) Then
varDN = Application.Match(CDbl(TextBox3.Value), _
Worksheets("CE Tables").Range("O:O"), 0)
End If
End If

If IsError(varDN) Then
Me.Label1.Caption = "Non-Standard DN Value"
Me.TextBox3.Value = ""
Cancel = True
End If

End Sub
Private Sub UserForm_Initialize()
With Me.CommandButton1
.Caption = "Ok"
.Default = True
End With
With Me.CommandButton2
.Caption = "Cancel"
.Cancel = True
'allow you to click cancel if you're in textbox3.
.TakeFocusOnClick = False
End With
With Me.Label1
.Caption = ""
.ForeColor = vbRed
End With
End Sub


aftamath77 wrote:

I have a userform with textbox3. The user enters a value into textbox3.
When the user moves to the next userform control, I want VBA to check the
value entered in textbox3 against a table I have on a worksheet (CE Tables).
If the value exists in the table, the focus will move to the next control,
but if the value doesn't exist, I want a msgbox to give a warning, then clear
the value and return to textbox3. Currently, I get a "Type Mismatch" with
MsgBox varDN.Value.

Private Sub TextBox3_Exit(ByVal Cancel As MSForms.ReturnBoolean)

Dim varDN As Variant

varDN = Application.VLookup(TextBox3.Value, Worksheets("CE
Tables").Range("O:O"), 1, False)
MsgBox varDN.Value

If IsError(varDN) = True And TextBox3.Value < "" Then
MsgBox "Non-Standard DN Value"
TextBox3.Value = ""
Textbox3.SetFocus
End If

End Sub

--

Dave Peterson

Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
VLOOKUP Problem, Please Help! Susan Excel Discussion (Misc queries) 2 September 4th 08 05:05 PM
VLOOKUP problem Ravenbear Excel Discussion (Misc queries) 3 July 24th 06 09:54 PM
Vlookup problem.. Neo1 Excel Worksheet Functions 5 March 16th 06 09:53 AM
vlookup problem shrutikhurana Excel Discussion (Misc queries) 2 February 9th 06 01:52 PM
Vlookup problem Big Jones Excel Worksheet Functions 0 August 18th 05 02:33 PM


All times are GMT +1. The time now is 02:53 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement