CODEWITHSUNDEEP
excelvbscriptvba
arun_roy
arun_roy

Reputation: 601

highlight on the cell which is being searched over a column

This is I am asking from my curiosity, that I have a Loop which is rolling over a column to match ID,Now after some iterations the script getting closed with a error message: "Match not found" 

 ParentMatchRowNum=objExcel1.Application.WorksheetFunction.Match(ParentID, ob3.Columns(1), 0)

So i am looking for a any kind of highlight with color,with its current ID search,just to point out the ID which is not being found by the script. Highlight will needs to be on the current ID which just has passed the search.

ERROR

Error

Thanks

Upvotes: 0

Views: 60

Answers (1)

bonCodigo
bonCodigo

Reputation: 14361

Try Application.Match instead of Application.WorksheetFunction.Match.

If not IsError(objExcel1.Application.Match(ParentID, ob3.Columns(1), 0)) Then    
   ParentMatchRowNum = objExcel1.Application.Match(ParentID, ob3.Columns(1), 0)
Else
   '-- do something
End if

Here is a little info that a MVP has shared: Difference between Application.Match() vs Application.WorkSheetFunction.Match()

The difference lies in its error handling. In XL97, there was a problem with the WorksheetFunction form of Match that did not manifest with Application.Match. Myrna Larson reports that she has experienced that problem in later versions. For that reason, it is generally agrreed to be safe rather than sorry and use Application.Match.

:-Reference.

One other thing is, just be aware you shouldn't search for a String when there are only integers in the column or mix data types. Keep things consistent. So for e.g. look for integers within integers or Strings within Strings.. Which means you should probably convert all of your alphanumeric ID references to CStr(ID). (Variants are interpreted at runtime, so it doesn't solve the issue.)

Upvotes: 1

Related Questions