Return msgbox if value not found - VBA

[manny88]

Hi I am using a search function on a userform and want the ID entered in the textbox to search for everything in Column B. If the value is found then it will display in the other textboxes - this bit works for me.

What doesn't is if the value is Not found then show msgbox to say "value not found".

Private sub searchbutton_click()

Dim ID_ref as string
ID_ref = trim(search box.text)
Lastrow =worksheets("Admin").cells(Rows.count, 2).End(club).Row

If Trim(ID_ref) ="" Then
   Magbox"search field is blank"
   End if
   Exit sub

[B]If trim(worksheets("admin").cells(i, 2) <> Trim(ID_ref) Then
   Msgbox "item not found"
   End if
   Exit sub[/B]

If trim(worksheets("admin").cells(i, 2) = Trim(ID_ref) Then
Type.text = worksheets("admin").cells(i,4).value
Exit for
End if
Next
End sub

Bit in bold is what's not working. When I run the sub first condition works but then it doesn't look for the value i enter and nor does it return those that exist.

Any help with this will be greatly appreciated

 

[Rick Rothstein]

I haven't gone into your code in any depth, but this line stood out to me, in particular what I highlighted in red... shouldn't that be xlUp instead?

Lastrow =worksheets("Admin").cells(Rows.count, 2).End(club).Row

 

[manny88]

I haven't gone into your code in any depth, but this line stood out to me, in particular what I highlighted in red... shouldn't that be xlUp instead?

Lastrow =worksheets("Admin").cells(Rows.count, 2).End(club).Row

Hi thanks for the reply. Yes, apologies that's a typo. I had to write it out on the phone as the document is on a work computer. To confirm, I have it as xlUp

 

[Rick Rothstein]

I think you are going to have to wait till you get home and copy/paste your actual code. I copy/pasted what you posted and 5 lines of code lit up as having syntax errors.

 

[manny88]

Code should read as

Private sub searchbutton_click()

Dim ID_ref as string
ID_ref = trim(search box.text)
Lastrow =worksheets("Admin").cells(Rows.count, 2).End(xlUp).Row

If Trim(ID_ref) ="" Then
   Magbox"search field is blank"
   End if
   Exit sub

[B]If trim(worksheets("admin").cells(i, 2) <> Trim(ID_ref) Then
   Msgbox "item not found"
   End if
   Exit sub[/B]

If trim(worksheets("admin").cells(i, 2) = Trim(ID_ref) Then
Type.text = worksheets("admin").cells(i,4).value
Next
End if
End sub

 

[manny88]

I think you are going to have to wait till you get home and copy/paste your actual code. I copy/pasted what you posted and 5 lines of code lit up as having syntax errors.

So if you take out the is empty and does not equal code it works to the point where it searches the ID that is being searched for and the desired column(s)