CODEWITHSUNDEEP
excelvba
Wren
Wren

Reputation: 79

Find first occurrence of value in a range, going up

If I have a column as follows,

1
2
2
1
3
4
5

how would I find the first occurrence of a particular number going upwards from a known value, say 4. I know the position of 4 in the column and I want the row number of the first occurrence of 1 going upwards from 4 (row number 4 in this case).

My instinct was to run a FOR loop but I think there's a fairly straightforward method to this that I'm not familiar with.

Appreciate the help!

p.s - I realise another way to find this would be to find the last occurrence of a value in a range going from the first row to the position of 4. Happy to learn either or both.

Upvotes: 1

Views: 1921

Answers (1)

Kundan
Kundan

Reputation: 217

Sheet1.Range("M:M").Find(What:="1", SearchDirection:=xlPrevious, After:=Range("M12")).Address

Upvotes: 3

Related Questions