Arrrghhh..... I've been testing this code with much
frustration.

-Office 2000
I have a Variant called Value1 that's used to track which
cell is highlighted in a column of text data (email
addresses). I use the ActiveCell command when I select the
address that I want:

Cells(Row, Col).Select
Value1 = ActiveCell

I then try to find the data from Value1 in another column
of email address data with this:

Columns("C:C").Select
Selection.Find(what:=Value1).Activate

This results in the error:

Run-time error '91': Object Variable or With block
variable no set.

Now as a test, if I change the line:

Value1 = ActiveCell to
Value1 = ;"

The code does not error. Any clues???

When I'm using ActiveCell, the cell does contain
;. I step through the program and put
Value1 and ActiveCell in the Watch list and everything
appears OK up to the Selection.Find line. I've also tried
redefining Value1 as an integer, string, etc with no
luck....

Thanks for any help that can be provided!

Steve Slechta

This looks like beginners coding, so I will go through a few things that may
help you out.

I personally don't like to use the Select/Activate methods nor do I like to
use the Selection or any of the active<object stuff unless it's really
needed. You may have initially gotten the code from using the Macro
Recorder, which using the macro recorder does help with regards to the early
process of understanding how VBA works, but there's still a lot of issues
with it creating code that can be intercepted in too many different ways.

How can we avoid such issues that the macro recorder brings?

First, which ever objects/values we are going to refer to more than once
during the course of the code, we can declare variables for those
objects/values. Let's say we are going to refer to Worksheet, "Sheet1"
multiple times within workbook, "Book1.xls", and that is the only worksheet
we will be working with. We also will be looking for a value within a
range, we can then use the following code:

Dim WS as Worksheet, SearchRange as Range, C as Range, Cl as Long, Rw as
Long
Dim Rng as Range, EmailAddress as String, Dim I as Long
Set WS = Workbooks("Book1.xls").Worksheets("Sheet1")
Set SearchRange = WS.Range("EmailAddress")
Cl = SearchRange.Column
Rw = SearchRange.Row
For I = Rw To Rng.Rows + Rw - 1 Step 1
EmailAddress = WS.Cells(I,Cl).Value
Set C = Rng.Find(EmailAddress,,xlValues,xlWhole,,,False)
If Not C Is Nothing Then
'Email address has been found
End If
Next I

Notes:

This assumes you have named the column (not necessarily the entire column,
but at least the portion which has email addresses) with email addresses as
"EmailAddress" at the workbook level.

A couple of the arguments may be changed from the last time the Find was
used (Rather via code or the Find Dialog Box, which is the same dialog box
as the Find and Replace Dialog Box).

--
Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000

"Steve Slechta" wrote in message
...
Arrrghhh..... I've been testing this code with much
frustration.

-Office 2000
I have a Variant called Value1 that's used to track which
cell is highlighted in a column of text data (email
addresses). I use the ActiveCell command when I select the
address that I want:

Cells(Row, Col).Select
Value1 = ActiveCell

I then try to find the data from Value1 in another column
of email address data with this:

Columns("C:C").Select
Selection.Find(what:=Value1).Activate

This results in the error:

Run-time error '91': Object Variable or With block
variable no set.

Now as a test, if I change the line:

Value1 = ActiveCell to
Value1 = ;"

The code does not error. Any clues???

When I'm using ActiveCell, the cell does contain
;. I step through the program and put
Value1 and ActiveCell in the Watch list and everything
appears OK up to the Selection.Find line. I've also tried
redefining Value1 as an integer, string, etc with no
luck....

Thanks for any help that can be provided!

Steve Slechta

Thanks Ron. It ends up that I did not have the entire range
defined and I received the error because it could not find
the data.

I also just learned the command "On Error Resume Next" to
keep the error window from opening when no text is found.

Steve Slechta

-----Original Message-----
This looks like beginners coding, so I will go through a
few things that may help you out.

Ron,

You're correct. I am a beginner. The world of objects has
not come into focus for me yet. I'm at the same stage in a
C++ class I'm taking as well. I'm keeping the notes you
provided here so when objects start to make more sense, I
can implement this code. I may play with this code anyway
for the learning process.

I feel bad for posting this then figuring out that it was a
dumb mistake right after posting...... story of my life. ;)

Thanks for the help!

Steve Slechta

Don't feel so bad. That is what we are all here for, to help each other
learn and grow. When I first started out working in VBA, I initially used
the macro recorder in Excel to help me learn the code. Took me a while to
learn VBA (about 3 months), but I did eventually get it. However, since I
tended to be one of those users that wanted to do other things on the same
system while that particular instance of Excel was running code, I had to
learn VBA rather fast cause all of the Active/Select Objects/Methods were
creating issues for me otherwise that I didn't like.

A few of the other things that I use a lot within VBA are the
Intermediate/Watch Windows, Object Browser, Help files, and the various
other debugging tools (a few of which, I created my own methodology). It
took me some time to learn the best way to use these tools, but once I
gotten the hang of each of them with regards to how they work and what
information they provide, it made my tasks easier.

Most of my time here lately has been dealing with working in Access VBA as
I'm in the midst of creating a DB program.

--
Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000

"Steve Slechta" wrote in message
...

-----Original Message-----
This looks like beginners coding, so I will go through a
few things that may help you out.

Ron,

You're correct. I am a beginner. The world of objects has
not come into focus for me yet. I'm at the same stage in a
C++ class I'm taking as well. I'm keeping the notes you
provided here so when objects start to make more sense, I
can implement this code. I may play with this code anyway
for the learning process.

I feel bad for posting this then figuring out that it was a
dumb mistake right after posting...... story of my life. ;)

Thanks for the help!

Steve Slechta