I have written the following code to display the packaging standard of each part number as soon as part number is entered in cell C1.For part numbers which have multiple vendors it should ask vendor code which are stored in column D.Part numbers are stored in Column A and hyperlinked path is done in Column B.Now if part number is not present I want it to display "Item number not found".But insted it is showing a Run Time Error 91. Object Variable or With Block Variable not set and showing debug in line "If Application.CountIf(.Range("A:A"), myR.Value) > 1 Then"..
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$C$1" Then macro '<=============easier
Dim myR As Range, Vendor As String
Set myR = .Range("A:A").Find( _
If Application.CountIf(.Range("A:A"), myR.Value) > 1 Then
Vendor = InputBox("There is more than one - please enter the Vendor Code")
If Vendor = "" Then Exit Sub
On Error Resume Next
n = 0
n = Application.Match(Vendor, .Range("D:D"), 0)
If n = 0 Then
MsgBox "Vendor Not Found"
Set myR = .Cells(n, 1)
If Not myR Is Nothing Then
ThisWorkbook.FollowHyperlink myR.Offset(, 1).Value
MsgBox "Item Number Not Found. Please check The Number You have Entered"
You need to check if Not myR is Nothing right after the Set myR = ...
If it IS nothing then it errors when you're asking how many there are.
You can also suppress errors by On Error Resume Next, and test to see if it found an error by testing If Err.Number <> 0 Then 'some error just occurred. You can also reset that by Err.Clear
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here