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
CodePudding user response:
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.