Damn error 91!
 

OK you intelligent people, could you help me with this
simple bit of code.

It needs to search down from A1 to find the first
occurance of "c" for example, the search from the bottom
of column a to find the last one (all the "c"'s will be
continuous).

It works fine, unless one of the strings isn't in the
list, in which case it comes up with a run time error 91.

is there a condition i can put in for if the search
doesn't find anything, or alternatively, how do you use
findfirst and findlast functions?

All help very much appreciated,

TIA


The Code...


For a = 1 To 6

If a = 1 Then district = "A"
If a = 2 Then district = "B"
If a = 3 Then district = "C"
If a = 4 Then district = "D"
If a = 5 Then district = "E"
If a = 6 Then district = "F"

Range("A65000").End(xlUp).Select
n = ActiveCell.Row
Range("A1", "A" & n).Select

Selection.Find(What:=district, After:=ActiveCell,
LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByColumns,
SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
frst = ActiveCell.Row

Range("A" & n + 2).Select
Selection.Find(What:=district, After:=ActiveCell,
LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByColumns,
SearchDirection:=xlPrevious, MatchCase:= _
False, SearchFormat:=False).Select
lst = ActiveCell.Row

Range("A" & frst, "A" & lst).Select
Selection.EntireRow.Select

Selection.Copy
cgc.Activate
Range("A65000").End(xlUp).Select
ActiveCell.Offset(1, 0).Select
ActiveSheet.Paste

Next a

Damn error 91!
 

Jon,

Generally, when you are using Find, you should write your code
similar to the following:

Dim FoundCell As Range
Set FoundCell = Cells.Find(....)
If Not FoundCell Is Nothing Then
' Find was successful, do something with FoundCell
Else
' Not Found
End If


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



"jon" wrote in message
...
OK you intelligent people, could you help me with this
simple bit of code.

It needs to search down from A1 to find the first
occurance of "c" for example, the search from the bottom
of column a to find the last one (all the "c"'s will be
continuous).

It works fine, unless one of the strings isn't in the
list, in which case it comes up with a run time error 91.

is there a condition i can put in for if the search
doesn't find anything, or alternatively, how do you use
findfirst and findlast functions?

All help very much appreciated,

TIA


The Code...


For a = 1 To 6

If a = 1 Then district = "A"
If a = 2 Then district = "B"
If a = 3 Then district = "C"
If a = 4 Then district = "D"
If a = 5 Then district = "E"
If a = 6 Then district = "F"

Range("A65000").End(xlUp).Select
n = ActiveCell.Row
Range("A1", "A" & n).Select

Selection.Find(What:=district, After:=ActiveCell,
LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByColumns,
SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
frst = ActiveCell.Row

Range("A" & n + 2).Select
Selection.Find(What:=district, After:=ActiveCell,
LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByColumns,
SearchDirection:=xlPrevious, MatchCase:= _
False, SearchFormat:=False).Select
lst = ActiveCell.Row

Range("A" & frst, "A" & lst).Select
Selection.EntireRow.Select

Selection.Copy
cgc.Activate
Range("A65000").End(xlUp).Select
ActiveCell.Offset(1, 0).Select
ActiveSheet.Paste

Next a