miami2k

Board Regular
Joined
Nov 1, 2017
Messages
58
Dear All,

I have this code that I've copied from somewhere. It works. I want the user to fill the field by typing. The problem is that if the user types a name that is not the list, excel returns an error because it does not find the value Rigaforn in the target range.

Private Sub CMBForn_Change()

Application.ScreenUpdating = False


'Trova riga fornitore
Dim Clienti As Worksheet
Set Clienti = Sheets("Clienti")
Set Cerca = Clienti.Range("B:B").Find(What:=Me.CMBForn.Value)
Dim RigaForn As Variant
RigaForn = Cerca.Row


'Copia dati
Dim BConf As Worksheet
Set BConf = Sheets("BConfirmation")
BConf.Unprotect "123"
BConf.Cells(10, 1) = Clienti.Cells(RigaForn, 2)
BConf.Cells(11, 1) = Clienti.Cells(RigaForn, 3)
BConf.Cells(12, 1) = Clienti.Cells(RigaForn, 4)
BConf.Cells(13, 1) = Clienti.Cells(RigaForn, 5)
BConf.Cells(13, 2) = Clienti.Cells(RigaForn, 6)
BConf.Cells(14, 1) = Clienti.Cells(RigaForn, 7)
BConf.Cells(15, 1) = Clienti.Cells(RigaForn, 8)


ActiveSheet.Protect "123", True, True


End Sub

I would like to know which code I should put after:
Set Cerca = Clienti.Range("B:B").Find(What:=Me.CMBForn.Value)
to avoid the error.
Something like on error... nothing should happen! :)


Thank you,


miami2k
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
How about
Code:
Dim Clienti As Worksheet
[COLOR=#0000ff]Dim Cerca As Range[/COLOR]
Set Clienti = Sheets("Clienti")
Set Cerca = Clienti.Range("B:B").Find(What:=Me.CMBForn.Value)
[COLOR=#0000ff]If Cerca Is Nothing Then Exit Sub
[/COLOR]
 
Upvote 0
Glad to help & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,185
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top