Match Function use in VBA

[EssoExplJoe]

I am trying to use the Match funcition in VBA but always get an error: Below is my code

dim ctable as range, wstab as worksheet
Set wstab = Worksheets("CUSPIDTable")
Set ctable = Range(wstab.Cells(4, 6), wstab.Cells(100, 6))
row = Application.Match("34567923", ctable.Address, 0)

The Match always returns an error if or not if the value "34567923" is in the range. I have tried just using "ctable" without the .address but it doesn't work either.

What is the proper syntax for using a named range in the match function

 

[EssoExplJoe]

Found a syntax that works after a couple of hours trying different things. There may be a more elegant solution but the below works:

row = Application.Match(cuspid, wstab.Range(ctable.Address), 0)

 

[Osvaldo Palmeiro]

Hi.
If the values in the "ctable" range contain numbers and not numbers formatted as text, then the command below should work.
The searched value should be without double quotes.

lngRow = Application.Match(34567923, ctable, 0)

 

[EssoExplJoe]

What I ended up doing was assigning the item to be match to a Variant type variable then it doesn't matter if the value to be matched is a number or a text. Thks for pointing this out. Final code I use is:

 

[EssoExplJoe]

What I ended up doing was assigning the item to be match to a Variant type variable then it doesn't matter if the value to be matched is a number or a text. Thks for pointing this out. Final code I use is:

dim ctable as range, wstab as worksheet, matchvalue as variant
Set wstab = Worksheets("CUSPIDTable")
Set ctable = Range(wstab.Cells(4, 6), wstab.Cells(100, 6))
matchvalue = "34567923"
row = Application.Match("34567923", ctable.Address, 0)  'this works
matchvalue = 34567923
row = Application.Match("34567923", ctable.Address, 0)  ''this works also