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

[Mark246]

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

 

[Guest]

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,"@")

 

[Mark246]

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

 

[Dave Peterson]

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?

 

[Mark246]

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.

 

[Dave Peterson]

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.

 

[Mark246]

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.