workshhetfunction match

[Sanjoy]

I Have a data workshhet where data is set by columns. but when I add new column a error is display" unable to get Worksheet.Function match class" what can I do

 

[Yongle]

Unlike Excel , VBA does not re-reference

Workaroud - use named range for your range

Example

1. Enter apple in cell E4 in Sheet1

2. Create named range with
name
myRange
RefersTo
=Sheet1!$E:$E

3. Add this code

Sub test()
    x = WorksheetFunction.Match("Apple", Range("myRange"), 0)
    MsgBox x
End Sub

4. Run code

5. Insert a new column before column B

6. Run code