ExcelBanter - View Single Post

Pele

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