CODEWITHSUNDEEP
excelvba
papeboi
papeboi

Reputation: 85

My Find function for a range, keeps starting at the 2nd instance, What am I doing wrong?

I'm trying to search through an array, then make adjustments to the row found. The find starts with the 2nd instance of what was found, how do I make this the first instance instead?

val = MySheet.Range("B8")
With MySheet.Range("MyList")
Set Loc = .Find(val, LookAt:=xlWhole, SearchOrder:=xlByRows)

Do
ListPos = Loc.Row
MyVal = MySheet.Cells(ListPos, 6).Value
MsgBox ListPos

Val = x MyList = {x,z,y,f,g,x,x} (just a 1 column range I named)

The MsgBox always shows the row value of the 2nd instance in the array

I've tried using the search order and I've tried using After:=0, it hasn't helped

I expected ListPos to be 1 instead it is always 6

Upvotes: 0

Views: 43

Answers (1)

QuickSilver
QuickSilver

Reputation: 770

I think you need After:= example below.

Set foundMatch = .Find(What:=cellCriteria, After:=.Cells(1, 1), LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True, SearchFormat:=False) 'finds a match

Upvotes: 1

Related Questions