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

Thanks...

"Jake Marx" wrote:

Hi Pele,

In order to match a cell's value exactly, you can specify another argument
to the Find method:

Set rng = Columns(3).Find(What:="total", LookAt:=xlWhole)

To store it as a variable, use a Long:

Dim lEndRow As Long

And assign it to the variable instead of using MsgBox:

If Not rng Is Nothing Then lEndRow = rng.Row

Now for your code:

s = 0
z = Cells(s, 3).Value

This won't work, as cells is 1-based (0 means row zero, which doesn't make
sense).

Do
s = s + 1
Cells(s, 3).Select
z = Cells(s, 3)

There's no need to Select anything here - just use z = Cells(s, 3).Value.

If z = "total" Then

String comparisons are case-sensitive by default. So if your cell value has
"Total" or "TOTAL", this won't return True. To do a case-insensitive match,
you can use StrComp():

If StrComp(z, "total", vbTextCompare) = 0 Then
'/ match
Else
'/ no match
End If

That said, a Find will be much quicker than iterating over the cells in the
column.

--
Regards,

Jake Marx
www.longhead.com


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

Pele wrote:
Jake,

I tried this solution but it didn't work the way I wanted. I wanted
to find the cell that said exactly "TOTAL" as opposed to "TOTAL
SUPPLY".

I also need to capture the row number as a variable to use in another
line of code (as opposed to it being displayed in a message box). I
am not that good at macro.

By the way, can you tell me why the code I'd written didn't work (see
below).

Pele

<snip