Hi Group,

I am having trouble with the follwing. When I try and find, it says it is
not set.
Dim SerialNumber As String
Set SerialNumber = ActiveCell.Offset(1, 3).Value & ActiveCell.Offset(1,
4).Value
Selection.Find(What:=SerialNumber, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

I tried the last statement with the Variable SerialNumber in ( ), but it
still says it is not set.

Thanks
--
David

Don't use the Set statement. Set statements are used for object
variables, not simple variables, as is your SerialNumber string.
Change

Set SerialNumber = ActiveCell.Offset(1, 3).Value &
ActiveCell.Offset(1, 4).Value
to
SerialNumber = ActiveCell.Offset(1, 3).Value &
ActiveCell.Offset(1, 4).Value

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"David" wrote in message
...
Hi Group,

I am having trouble with the follwing. When I try and find, it
says it is
not set.
Dim SerialNumber As String
Set SerialNumber = ActiveCell.Offset(1, 3).Value &
ActiveCell.Offset(1,
4).Value
Selection.Find(What:=SerialNumber, After:=ActiveCell,
LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows,
SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

I tried the last statement with the Variable SerialNumber in
( ), but it
still says it is not set.

Thanks
--
David

Hello David,

You do not need to Set anything except Objects, which String is not. Here
is an example of what you could use ...

Dim rngCell As Range
Dim SerialNumber As String
SerialNumber = ActiveCell.Offset(1, 3).Value & ActiveCell.Offset(1,
4).Value
Set rngCell = Selection.Find(What:=SerialNumber, LookIn:=xlFormulas,
LookAt:=xlWhole)
If Not rngCell Is Nothing Then
rngCell.Activate
Else
MsgBox

HTH

--
Regards,
Zack Barresse, aka firefytr
To email, remove NOSPAM


"David" wrote in message
...
Hi Group,

I am having trouble with the follwing. When I try and find, it says it is
not set.
Dim SerialNumber As String
Set SerialNumber = ActiveCell.Offset(1, 3).Value & ActiveCell.Offset(1,
4).Value
Selection.Find(What:=SerialNumber, After:=ActiveCell, LookIn:=xlFormulas,
_
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

I tried the last statement with the Variable SerialNumber in ( ), but it
still says it is not set.

Thanks
--
David

... must have missed the End If when copying over, btw.

--
Regards,
Zack Barresse, aka firefytr
To email, remove NOSPAM


"Zack Barresse" wrote in message
...
Hello David,

You do not need to Set anything except Objects, which String is not. Here
is an example of what you could use ...

Dim rngCell As Range
Dim SerialNumber As String
SerialNumber = ActiveCell.Offset(1, 3).Value & ActiveCell.Offset(1,
4).Value
Set rngCell = Selection.Find(What:=SerialNumber, LookIn:=xlFormulas,
LookAt:=xlWhole)
If Not rngCell Is Nothing Then
rngCell.Activate
Else
MsgBox

HTH

--
Regards,
Zack Barresse, aka firefytr
To email, remove NOSPAM


"David" wrote in message
...
Hi Group,

I am having trouble with the follwing. When I try and find, it says it is
not set.
Dim SerialNumber As String
Set SerialNumber = ActiveCell.Offset(1, 3).Value & ActiveCell.Offset(1,
4).Value
Selection.Find(What:=SerialNumber, After:=ActiveCell, LookIn:=xlFormulas,
_
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

I tried the last statement with the Variable SerialNumber in ( ), but it
still says it is not set.

Thanks
--
David

Set is only required when you are referencing objects like range objects or
whape objects. In your case SerialNumber is a regualr variable (string, or
integer or double), so set is not required. However if your find does not
find what it is looking for the code will crash. This is where you would want
to set a range object

Dim SerialNumber As String
Dim rngFound as Range

SerialNumber = ActiveCell.Offset(1, 3).Value & ActiveCell.Offset(1, 4).Value

set rngFound = Selection.Find(What:=SerialNumber, After:=ActiveCell,
LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)

if rngfound is nothing then
msgbox "Sorry, not found"
else
rngfound.select
enid if

--
HTH...

Jim Thomlinson


"David" wrote:

Hi Group,

I am having trouble with the follwing. When I try and find, it says it is
not set.
Dim SerialNumber As String
Set SerialNumber = ActiveCell.Offset(1, 3).Value & ActiveCell.Offset(1,
4).Value
Selection.Find(What:=SerialNumber, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

I tried the last statement with the Variable SerialNumber in ( ), but it
still says it is not set.

Thanks
--
David

I tried to do a subsitution on your find statement, but this is failing, even
though the string exists. I am trying to put the string into a variable, but
i thought i might and should be able to do it with the constant.

Set rngFound = Cells.Find(What:="9C4754", After:=ActiveCell,
LookIn:=xlFormulas, _ LookAt :=xlWhole, SearchOrder:=xlByRows,
SearchDirection:=xlNext, _ MatchCase:= _ False, SearchFormat:=False).Activate
--

Thanks for your help Jim,
David


"Jim Thomlinson" wrote:

Set is only required when you are referencing objects like range objects or
whape objects. In your case SerialNumber is a regualr variable (string, or
integer or double), so set is not required. However if your find does not
find what it is looking for the code will crash. This is where you would want
to set a range object

Dim SerialNumber As String
Dim rngFound as Range

SerialNumber = ActiveCell.Offset(1, 3).Value & ActiveCell.Offset(1, 4).Value

set rngFound = Selection.Find(What:=SerialNumber, After:=ActiveCell,
LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)

if rngfound is nothing then
msgbox "Sorry, not found"
else
rngfound.select
enid if

--
HTH...

Jim Thomlinson


"David" wrote:

Hi Group,

I am having trouble with the follwing. When I try and find, it says it is
not set.
Dim SerialNumber As String
Set SerialNumber = ActiveCell.Offset(1, 3).Value & ActiveCell.Offset(1,
4).Value
Selection.Find(What:=SerialNumber, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

I tried the last statement with the Variable SerialNumber in ( ), but it
still says it is not set.

Thanks
--
David

Just my two cents - try removing Activate from the end of your set statement.


"David" wrote:

I tried to do a subsitution on your find statement, but this is failing, even
though the string exists. I am trying to put the string into a variable, but
i thought i might and should be able to do it with the constant.

Set rngFound = Cells.Find(What:="9C4754", After:=ActiveCell,
LookIn:=xlFormulas, _ LookAt :=xlWhole, SearchOrder:=xlByRows,
SearchDirection:=xlNext, _ MatchCase:= _ False, SearchFormat:=False).Activate
--

Thanks for your help Jim,
David


"Jim Thomlinson" wrote:

Set is only required when you are referencing objects like range objects or
whape objects. In your case SerialNumber is a regualr variable (string, or
integer or double), so set is not required. However if your find does not
find what it is looking for the code will crash. This is where you would want
to set a range object

Dim SerialNumber As String
Dim rngFound as Range

SerialNumber = ActiveCell.Offset(1, 3).Value & ActiveCell.Offset(1, 4).Value

set rngFound = Selection.Find(What:=SerialNumber, After:=ActiveCell,
LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)

if rngfound is nothing then
msgbox "Sorry, not found"
else
rngfound.select
enid if

--
HTH...

Jim Thomlinson


"David" wrote:

Hi Group,

I am having trouble with the follwing. When I try and find, it says it is
not set.
Dim SerialNumber As String
Set SerialNumber = ActiveCell.Offset(1, 3).Value & ActiveCell.Offset(1,
4).Value
Selection.Find(What:=SerialNumber, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

I tried the last statement with the Variable SerialNumber in ( ), but it
still says it is not set.

Thanks
--
David

I am still failing and I guess it is because I have not enclosed all the
code, so the problems must be somewhere else:
Sub TaylorFarm()
' Macro1 Macro
' Macro recorded 3/23/2006 by David Lanman
Range("A1").Select
Dim SerialNumber As String
Dim rngFound As Range
Cells.Find(What:="Equipment", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
EquipmentID = ActiveCell.Offset(1, 0).Value
If EquipmentID = "" Then
SerialNumber = ActiveCell.Offset(1, 3).Value & ActiveCell.Offset(1,
4).Value
Sheets("TAYLORSerNo").Select
Range("A1").Select
Set rngFound = Cells.Find(What:="9C4754", After:=ActiveCell, _
LookIn:=xlFormulas, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
If rngFound Is Nothing Then
MsgBox "Sorry, not found"
Else
rngFound.Select
End If
Else
Stop
End If
Cells.FindNext(After:=ActiveCell).Activate
End Sub

It fails when I try and do the Find "Set rngFound =". It is hardcoded here,
but I would be using the variable SerialNumber.

Thanks for your help.

--
David


"Zack Barresse" wrote:

... must have missed the End If when copying over, btw.

--
Regards,
Zack Barresse, aka firefytr
To email, remove NOSPAM


"Zack Barresse" wrote in message
...
Hello David,

You do not need to Set anything except Objects, which String is not. Here
is an example of what you could use ...

Dim rngCell As Range
Dim SerialNumber As String
SerialNumber = ActiveCell.Offset(1, 3).Value & ActiveCell.Offset(1,
4).Value
Set rngCell = Selection.Find(What:=SerialNumber, LookIn:=xlFormulas,
LookAt:=xlWhole)
If Not rngCell Is Nothing Then
rngCell.Activate
Else
MsgBox

HTH

--
Regards,
Zack Barresse, aka firefytr
To email, remove NOSPAM


"David" wrote in message
...
Hi Group,

I am having trouble with the follwing. When I try and find, it says it is
not set.
Dim SerialNumber As String
Set SerialNumber = ActiveCell.Offset(1, 3).Value & ActiveCell.Offset(1,
4).Value
Selection.Find(What:=SerialNumber, After:=ActiveCell, LookIn:=xlFormulas,
_
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

I tried the last statement with the Variable SerialNumber in ( ), but it
still says it is not set.

Thanks
--
David




.

Hi,

Remove the Activate method when you are making an assignment.

Set rngFound = Cells.Find(What:="9C4754", After:=ActiveCell, _
LookIn:=xlFormulas, LookAt _
:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False)

Cheers
Andy

David wrote:
I am still failing and I guess it is because I have not enclosed all the
code, so the problems must be somewhere else:
Sub TaylorFarm()
' Macro1 Macro
' Macro recorded 3/23/2006 by David Lanman
Range("A1").Select
Dim SerialNumber As String
Dim rngFound As Range
Cells.Find(What:="Equipment", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
EquipmentID = ActiveCell.Offset(1, 0).Value
If EquipmentID = "" Then
SerialNumber = ActiveCell.Offset(1, 3).Value & ActiveCell.Offset(1,
4).Value
Sheets("TAYLORSerNo").Select
Range("A1").Select
Set rngFound = Cells.Find(What:="9C4754", After:=ActiveCell, _
LookIn:=xlFormulas, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
If rngFound Is Nothing Then
MsgBox "Sorry, not found"
Else
rngFound.Select
End If
Else
Stop
End If
Cells.FindNext(After:=ActiveCell).Activate
End Sub

It fails when I try and do the Find "Set rngFound =". It is hardcoded here,
but I would be using the variable SerialNumber.

Thanks for your help.


--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

You're setting a variable, which you do not need to Activate it. You can,
however, Activate it afterwards if you'd like, and that is only if it found
what you were looking for. A simple test would look like this...

If rngFound Is Nothing Then
Msgbox "Nothing was found!"
Else
rngFound.Activate
MsgBox "Cell found at " & rngFound.Address
End If

--
Regards,
Zack Barresse, aka firefytr
To email, remove NOSPAM


"David" wrote in message
...
I tried to do a subsitution on your find statement, but this is failing,
even
though the string exists. I am trying to put the string into a variable,
but
i thought i might and should be able to do it with the constant.

Set rngFound = Cells.Find(What:="9C4754", After:=ActiveCell,
LookIn:=xlFormulas, _ LookAt :=xlWhole, SearchOrder:=xlByRows,
SearchDirection:=xlNext, _ MatchCase:= _ False,
SearchFormat:=False).Activate
--

Thanks for your help Jim,
David


"Jim Thomlinson" wrote:

Set is only required when you are referencing objects like range objects
or
whape objects. In your case SerialNumber is a regualr variable (string,
or
integer or double), so set is not required. However if your find does not
find what it is looking for the code will crash. This is where you would
want
to set a range object

Dim SerialNumber As String
Dim rngFound as Range

SerialNumber = ActiveCell.Offset(1, 3).Value & ActiveCell.Offset(1,
4).Value

set rngFound = Selection.Find(What:=SerialNumber, After:=ActiveCell,
LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)

if rngfound is nothing then
msgbox "Sorry, not found"
else
rngfound.select
enid if

--
HTH...

Jim Thomlinson


"David" wrote:

Hi Group,

I am having trouble with the follwing. When I try and find, it says it
is
not set.
Dim SerialNumber As String
Set SerialNumber = ActiveCell.Offset(1, 3).Value & ActiveCell.Offset(1,
4).Value
Selection.Find(What:=SerialNumber, After:=ActiveCell,
LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext,
_
MatchCase:=False, SearchFormat:=False).Activate

I tried the last statement with the Variable SerialNumber in ( ), but
it
still says it is not set.

Thanks
--
David