CODEWITHSUNDEEP
excelvba
spiNOops
spiNOops

Reputation: 99

How to use Application.Match and ignore the first zeros in a string?

Here is my code but the problem is sometimes, my strings (in a loop) have some zeros in the beginning. So I would like to ignore them :

Dim TableControle As ListObject
Set TableControle = ThisWorkbook.Worksheets("Test").ListObjects("myTable")

'Loop on all lines
For i = 1 To LastRow
           
 Dim matchResult As Variant
 Dim searchFichier As String
 'my value to search
 searchFichier = "123"
        
 'The value in the columns TableControle.ListColumns(1).DataBodyRange could be 00123 or 00456, with 0 in the beginning
  matchResult = Application.Match(searchFichier, TableControle.ListColumns(1).DataBodyRange, 0)
    
  If IsError(matchResult) Then
   'not found
  Else
   'value found
  End If
    
Next i

I tried to use .Find but it indicates a compilation error (surely I don't know how to use it) :

 matchResult = .Find(What:=mySearch, _
        LookAt:=TableControle.ListColumns(1).DataBodyRange, _
        MatchCase:=False)

Could you help me?

Thanks !

Upvotes: 1

Views: 88

Answers (1)

Pᴇʜ
Pᴇʜ

Reputation: 57683

The issue is that if you define Dim searchFichier As String it is looking for the text "123" but not for a number. Since you probably have numbers in your column it does not match anything.

Therfore you have to change it to

Dim searchFichier As Long
searchFichier = 123

for example then it will find a number.

Upvotes: 1

Related Questions