WorksheetFunction.Match miss match

Question

I don't get the same result for those two lignes. Can someone explain me?

    Dim mnt As String
    Dim I As Integer

Ligne = 3

'Case 1: Working
mnt = Cells(WorksheetFunction.Match(r.Offset(, -4), Sheets(7).Range("I3:I200"), 0) + 2, 9).Address
    
'Case 2: Not working
mnt = Cells(WorksheetFunction.Match(r.Offset(, -4), Sheets(7).Range("I" & Ligne & ":I200"), 0) + 2, 9).Address

But if I do Range("I" & Ligne & ":I200").select it select correctly the range I3:I200

In the case 2, there's no error message, it juste seams to always return the value of I3 instead of searching in I3:I200.

Can someone explain me why it doesn't work in the second case?

thank you

Answer 1

In response to your comments, I would suggest switching methods to use Range.Find and .FindNext. These two methods can quickly search a sheet for a string value and return its position. The FindNext method allows you to repeat the search, finding other cells with the same string value.

Here is a simple example of how to make a .Find and .FindNext loop.

Sub Example()
    'Find all instances of "Steve" on the activesheet and highlight them
    Call Highlight("Steve")
End Sub

Sub Highlight(FindText As String, Optional WithinSheet As Worksheet)
    If WithinSheet Is Nothing Then Set WithinSheet = ActiveSheet

    Dim rFirst As Range
    Set rFirst = WithinSheet.Cells.Find(What:=FindText, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)
    
    Dim rNext As Range
    Set rNext = rFirst
    Do
        Set rNext = WithinSheet.Cells.FindNext(After:=rNext)
        rNext.Interior.Color = 65535
    Loop While Not rNext.Address = rFirst.Address
End Sub

To create your own sub using this idea, you can replace the line rNext.Interior.Color = 65535 with any other things you would like done to each cell found during this loop. For example you could do rNext.Offset(0,1) = "Here!" to insert some text beside each found cell.