Excel 2000: How to find a certain character in ANY Column?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Mark246 - 09 May 2007 00:24 GMT

Using Excel 2000, I posted here and John Bundy told us how to select
only the Rows that have a certain character (@), by using a "Helper"
column with
=IF(ISERROR(FIND("@",A1)),"","1")
Cool. That worked magnificently.

Now,... for another file, I need to do the same thing, but the email
address might be in Any Column. I tried
=IF(ISERROR(FIND("@",A1:Z1)),"","1")
but that didn't work.

I need to select only the Rows... that could have an email address in
any Column.

Can it be done?

Thanks very much, in advance, people.

Mark246

Reply to this Message

Barb Reinhardt - 09 May 2007 02:47 GMT

Try adding this function to the workbook code

Function FindPattern(myrange As Range, myPattern As String)
Dim r As Range

myPattern = "*" & myPattern & "*"
FindPattern = ""
For Each r In myrange
If r.Text Like myPattern Then
FindPattern = 1
Exit Function
End If
Next r

End Function

In the cell, enter =FINDPATTERN(A1:Z1,"@")

> Using Excel 2000, I posted here and John Bundy told us how to select
> only the Rows that have a certain character (@), by using a "Helper"
[quoted text clipped - 15 lines]
>
> Mark246

Reply to this Message

Mark246 - 15 May 2007 07:03 GMT

Thanks, Barb.

Wow. This is new to me, but I'm trying...
I've read the "Getting started with User Defined Functions",
created the "personal.xls" file,
entered that text (from "Function..." down to "End Function") starting
in A1,
saved that personal.xls file in the XLSTART directory,
opened my datafile.xls,
inserted a column 1 and put "=personal.xls!findpattern(B1:Z1,"@")" in
A1,
and it says #NAME?.

Help, please?

Thanks very much, in advance, people.

Mark246

Reply to this Message

Dave Peterson - 15 May 2007 12:50 GMT

And you allow macros to run (tools|macro|security)?

And did you put findpattern in a general module--not behind a worksheet and not
behind ThisWorkbook?

> Thanks, Barb.
>
[quoted text clipped - 14 lines]
>
> Mark246

Signature

Dave Peterson

Reply to this Message

Mark246 - 19 May 2007 06:49 GMT

Thanks very much for the reply, Dave.

Security WAS set at High; I set it to Medium.

You asked: "And did you put findpattern in a general module--not
behind a worksheet and not behind ThisWorkbook?" I dunno' what that
means. Still,...

When I went to "Tools/Macro/Security", I saw the "Macro/
RecordNewMacro".
Maybe I didn't RECORD or SAVE the macro how it should have been saved.
I tried the RecordNewMacro thing, and re-typed the entire thing,
starting at a random cell... G8
IN the personal.xls file. Then saved it.
Then exited Excel.
Then restarted it.

It still doesn't work.

Any ideas, people? Thanks in advance.

Reply to this Message

Dave Peterson - 19 May 2007 12:51 GMT

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Short course:

Open your workbook.
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste the code in there.

=====
And since I don't know where you put the other code, you may want to delete it.

> Thanks very much for the reply, Dave.
>
[quoted text clipped - 16 lines]
>
> Any ideas, people? Thanks in advance.

Signature

Dave Peterson

Reply to this Message

Mark246 - 21 May 2007 20:25 GMT

COOL ! It Works! Thanks a bunch, Dave.

I had already read David McRitchie's intro... still couldn't quite
figure it out. Your abbreviated instructions were great.

Thanks again.

Reply to this Message