De: Myrna Larson Objet: Re: find the N'th occurence of a string Date : samedi 18 septembre 1999 03:39 On Fri, 17 Sep 1999 18:40:03 GMT, 8284@my-deja.com wrote: >at this point I just want to make sure that it can find a >particular - an N'th - occurrence of a specific string in the cells of a >particular column. It starts from the bottom and hits the first >occurrence, after which they go one after another. As soon as it reaches >the first cell that does not contain that string (there will be more of >the cells with the same string up top, but those will appear only after >a cell with some other value), it should stop and select that area >below that row. I don't see any reference to a variable N in your code. do you mean by nth, the last occurrence? There are a couple of problems with your find statement, which may explain why the macro isn't working correctly. 1. You want SearchDirection, not SearchOrder here. (The latter specifies Byrows or ByColumns.) 2. You need to specify where to start, which would be B1 if you're searching backward for the last occurrence. Maybe the following does what you describe above. Sub XX() Dim myfindString As String Dim c As range Dim R1 As long, R2 As long myfindString = "TOTAL" set c = range("B:B").find(What:=myfindString, After:=range("B1"), _ LookIn:=xlValues, LookAt:=xlPart, Searchdirection:=xlPrevious) If c Is Nothing Then MsgBox myfindString & " not found!" Exit Sub End If R2 = c.Row For R1 = R2 - 1 To 1 Step -1 If Cells(R1, 2).Value <> myfindString Then Exit For Next R1 R1 = R1 + 1 range(Cells(R1, 2), Cells(R2, 2)).Select End Sub