Vlookup not returning a value

[cashmp]

Hi Trying to do a simple lookup but not getting any results
I have a combo box (cb1) and when i click on edit it should return the 4th column (Customer Name )value in to textbox1(tb1) on the form
Data is name of range a1:i4
it go to error 1004 debug

any ideas would be a help

thanks
in advance

Private Sub CommandButton1_Click()
If WorksheetFunction.CountIf(Sheet10.Range("b:b"), Me.CB1.Value) = 0 Then
MsgBox "No details to retrive"
End If
If Application.WorksheetFunction.CountIf(Worksheets("Database").Range("b:b"), Me.CB1.Value) = 1 Then
With Me
MsgBox "YES IT 1"



.TB1 = Application.WorksheetFunction.VLookup(CLng(Me.CB1), Sheet10.Range("Data"), 4, False)


End With
End If



End Sub

Date	Serial No	Instrument	Customer Name	Customer Address	Order No	Tag No	Model No	Serial No
	10159	it worked	noreen
15/Feb/22	10160		patsy
15/Feb/22	10161	PRESSURE INDICATOR	john	carrig	234rf	pi1452	pi789	4856

 

[Alex Blakenburg]

The below should stop it from erroring out when it is not found.
I suspect that on your lookup sheet your Serial no is text though.

So your text box is returning text, but you are converting it to a number with CLng and looking it up in Column B.
But if the column B numbers are in fact text then it won't find it.
To check change the Column B number format to 2 decimal places. Any numbers that change to show 2 decimal places are recognised as numbers the others are text.

With Application
    Me.TB1 = .IfError(.VLookup(CLng(Me.CB1), Sheet2.Range("Data"), 2, False), "Not Found")
End With

 

[cashmp]

The below should stop it from erroring out when it is not found.
I suspect that on your lookup sheet your Serial no is text though.

So your text box is returning text, but you are converting it to a number with CLng and looking it up in Column B.
But if the column B numbers are in fact text then it won't find it.
To check change the Column B number format to 2 decimal places. Any numbers that change to show 2 decimal places are recognised as numbers the others are text.

With Application
    Me.TB1 = .IfError(.VLookup(CLng(Me.CB1), Sheet2.Range("Data"), 2, False), "Not Found")
End With

Alex
Thanks for your reply and code
Yes that stopped the erroring and put not found in the textbox

but i changed the format of Column b and all the text changed to 2 decimal

on the sheet it self i did the vlookup in a cell and it works

I think myself that it something to do with the data range

i will keep looking

thanks
pat

 

[Alex Blakenburg]

Do you know how to use the Immediate window on VBA ?
Ctrl+G will activate it
Then with the main workbook active copy the below into the immediate window and hit return
?Sheet10.Range(Data").Address(0,0,xlA1,1)
See if what it returns makes sense.

Oh I noticed I didn't change my test code to back to match yours.
Did you change sheet2 back in mine to sheet10 and the lookup column from 2 back to 4

ie should be

With Application
       Me.TB1 = .IfError(.VLookup(CLng(Me.CB1), Sheet10.Range("Data"), 4, False), "Not Found")
End With

Also I think you are most likely looking up the Serial no in which case "Data" should be B1:I4 (what are you using in your manual Vlookup ?)

Data is name of range a1:i4

 

[cashmp]

Alex
Sorry I Am new to VBA trying to do simple stuff

yes i fixed the sheet2 to sheet10

so I Put sheet10.range("Data").Address (0,0x1A1,1)

and get reference isn't valid

and it highlights the word address

thanks again for your help
pat

 

[Alex Blakenburg]

Did you copy paste it in, it is not the same as what I sent ?

 

[cashmp]

Did you copy paste it in, it is not the same as what I sent ?

Yes I had to add " before data

 

[Alex Blakenburg]

You hit ctrl+g in the spreadsheet window.
The intent was to do it when you were in the vba window.

I have logged off for the night. If you don’t get it sorted then:

• Show me the in Spreadsheet vlookup formula that is working
• The value you are using in your vlookup ideally matching one of the lines in the sample data you posted in post #1
• the name of Sheet10

 

[cashmp]

You hit ctrl+g in the spreadsheet window.
The intent was to do it when you were in the vba window.

I have logged off for the night. If you don’t get it sorted then:

• Show me the in Spreadsheet vlookup formula that is working
• The value you are using in your vlookup ideally matching one of the lines in the sample data you posted in post #1
• the name of Sheet10

Alex

thanks for all the help i will look at it later today and if i dont get it i will post back to you

thanks again

pat

 

[cashmp]

You hit ctrl+g in the spreadsheet window.
The intent was to do it when you were in the vba window.

I have logged off for the night. If you don’t get it sorted then:

• Show me the in Spreadsheet vlookup formula that is working
• The value you are using in your vlookup ideally matching one of the lines in the sample data you posted in post #1
• the name of Sheet10

Alex
got it working my data range for vlookup was incorrect as it started at a1 and should have been b1

thanks again for you time and help

pat