Index and Match using with Named Range

[mhghg]

Hi guys,

I am learning index and match. I have created two named range CustomerList and tblName
when i try to collect the return value from index formula i have the error: Application or object not defined
Can you shed some light into it please?

With Sheets("Name")
Range("A1").Select
lRow = .Range("A" & Rows.Count).End(xlUp).Row
LCol = ActiveCell.End(xlToRight).Column
Set rng = ActiveCell.Resize(lRow, LCol)
Set tblName = ActiveCell.Resize(lRow, 1)

ThisWorkbook.Names.Add Name:="CustomerList", RefersTo:=rng, Visible:=True

InputVal = Application.InputBox(Prompt:="Enter Name", Title:="User Input")
Num = Application.WorksheetFunction.Index("CustomerList", WorksheetFunction.Match("& InputVal &", tblName, 1), 1)

End With

 

[Hui]

Try:

Num = Application.WorksheetFunction.Index("CustomerList", _
WorksheetFunction.Match( trim(cstr(InputVal)) , tblName, 1), 1)

 

[NARAYANK991]

Hi ,

I am not sure what you want to do ; I assume that the variable Num will store the number in column A. In this case , the range named tblName will need to store only the names , which means it will need to be defined accordingly.

See if this is what you want :

Public Sub test()
          With Sheets("Name")
                Range("A1").Select

                lRow = .Range("A" & Rows.Count).End(xlUp).Row
                LCol = ActiveCell.End(xlToRight).Column

                Set Rng = ActiveCell.Resize(lRow, LCol)
                Set tblName = ActiveCell.Offset(, 1).Resize(lRow, 1)

                ThisWorkbook.Names.Add Name:="CustomerList", RefersTo:=Rng, Visible:=True

                InputVal = Application.InputBox(Prompt:="Enter Name", Title:="User Input")
                Num = Application.WorksheetFunction.Index([CustomerList], WorksheetFunction.Match(InputVal, tblName, 0), 1)
                MsgBox Num
          End With
End Sub

Narayan

 

[mhghg]

Hi Narayan and Hui,
I found my error from Narayan script
1. Wrong calculation for tblName from Activecell so Named range does not work
2. No need to put InputVal inside " & InputVal & "

happy face !

 

[mhghg]

Hi Guy,
How do i Catch the error to inform the user if the input value is not in the predefined range,as I got the error : "Unable to get the Match property of the WorksheetFunction Class" when I enter new name

 

[NARAYANK991]

Hi ,

Try this :

Public Sub test()
          With Sheets("Name")
                Range("A1").Select

                lRow = .Range("A" & Rows.Count).End(xlUp).Row
                LCol = ActiveCell.End(xlToRight).Column

                Set Rng = ActiveCell.Resize(lRow, LCol)
                Set tblName = ActiveCell.Offset(, 1).Resize(lRow, 1)

                ThisWorkbook.Names.Add Name:="CustomerList", RefersTo:=Rng, Visible:=True

                InputVal = Application.InputBox(Prompt:="Enter Name", Title:="User Input")
                If IsError(Application.Match(InputVal, tblName, 0)) Then
                  MsgBox "Entered name does not exist !", vbCritical
                Else
                  Num = Application.WorksheetFunction.Index([CustomerList], WorksheetFunction.Match(InputVal, tblName, 0), 1)
                  MsgBox Num
                End If
          End With
End Sub

Narayan

 

[mhghg]

Cool! I learn a lot for this week .

 

[mhghg]

Hi guys,
I have another question about inputbox,if the user click cancel then how do we exit the sub as my inputVal is integer not variant, which cannot be checked with VB.boolean.
Macy

 

[NARAYANK991]

Hi ,

There are two types of Inputbox ; one is the InputBox function , which returns a String , the other is the Application.InputBox method which returns a Variant.

Try the following code , and see what is displayed , when you press the Cancel button.

Public Sub test()
          Dim x As Integer

          x = Application.InputBox("Enter", , , , , , , 2)
         
          Debug.Print TypeName(x), x
End Sub

Narayan

 

[mhghg]

Ok Thanks I worked it out now.