I have a data gathering template used in our department and the length of the
template could vary. Regardless of the length though, the row at the end of
the template will have the word TOTAL in column C of that row.

The USer can still add text into rows below the one identified as the end of
the template i.e. if cell C69 has the word, Total, then row 69 will be
assumed to be the ned of the template regardless of how many non-empty rows
are after that.

I tried to write a code to identify the end of the template(see below) but
it is not working. The Do Loop wasn't exited. ANy help will be appreciated.

Pele


Worksheets(2).Activate
s = 0
Do
s = s + 1
Cells(s, 3).Select
z = Cells(s, 3)
If z = "total" Then
Exit Do
End If
Loop
Size = z

Hi Pele,

You could use the Find method to do this:

Dim rng As Range

Set rng = Columns(3).Find(What:="total")
If Not rng Is Nothing Then MsgBox rng.Row

Set rng = Nothing

--
Regards,

Jake Marx
www.longhead.com


[please keep replies in the newsgroup - email address unmonitored]

Pele wrote:
I have a data gathering template used in our department and the
length of the template could vary. Regardless of the length though,
the row at the end of the template will have the word TOTAL in column
C of that row.

The USer can still add text into rows below the one identified as the
end of the template i.e. if cell C69 has the word, Total, then row 69
will be assumed to be the ned of the template regardless of how many
non-empty rows are after that.

I tried to write a code to identify the end of the template(see
below) but it is not working. The Do Loop wasn't exited. ANy help
will be appreciated.

Pele


Worksheets(2).Activate
s = 0
Do
s = s + 1
Cells(s, 3).Select
z = Cells(s, 3)
If z = "total" Then
Exit Do
End If
Loop
Size = z