On error goto specified Line


P

Peruanos72

I have the following code (the code repeats about 12 times searching for a
different name each time) and if the name being searched is not found then I
get a runtime error 91. I want the code to run everytime but if the item is
not found then I want the macro to continue to a specified line and continue
with the next search.

Thanks in advance for any help on this.

Sub Update_Non_Blue()
'
' find Macro
' Macro recorded 2/4/2009 by rblakeman
'

'
Range("A5").Select
Selection.QueryTable.Refresh BackgroundQuery:=False

Range("A1").Select

Cells.find(What:="ASHELTON", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
On Error GoTo 26
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
ActiveCell.Offset(0, -1).Range("A1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.FillDown
ActiveCell.Select
Selection.ClearContents

Range("A1").Select
Cells.find(What:="BAHOUST", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
ActiveCell.Offset(0, -1).Range("A1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.FillDown
ActiveCell.Select
Selection.ClearContents

Range("A1").Select
Cells.find(What:="CDMCNEAL", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
ActiveCell.Offset(0, -1).Range("A1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.FillDown
ActiveCell.Select
Selection.ClearContents
 
Ad

Advertisements

J

Joel

Sub Update_Non_Blue()
'
' find Macro
' Macro recorded 2/4/2009 by rblakeman
'

'
Range("A5").QueryTable.Refresh BackgroundQuery:=False

Set c = Cells.Find(What:="ASHELTON", LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If Not c Is Nothing Then
Set DataRange = Range(c, c.End(xlDown))
DataRange.Copy _
Destination:=c.Offset(0, -1)
Application.CutCopyMode = False

DataRange.ClearContents

firstAddress = c.Address
Do
Set c = Cells.Find(What:="BAHOUST", _
After:=c, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)


If Not c Is Nothing Then
Set DataRange = Range(c, c.End(xlDown))
DataRange.Copy _
Destination:=c.Offset(0, -1)
Application.CutCopyMode = False

DataRange.ClearContents
End If
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
Set c = Cells.Find(What:="CDMCNEAL", LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If Not c Is Nothing Then
Set DataRange = Range(c, c.End(xlDown))
DataRange.Copy _
Destination:=c.Offset(0, -1)
Application.CutCopyMode = False

DataRange.ClearContents

firstAddress = c.Address
Do
Set c = Cells.Find(What:="BAHOUST", _
After:=c, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)


If Not c Is Nothing Then
Set DataRange = Range(c, c.End(xlDown))
DataRange.Copy _
Destination:=c.Offset(0, -1)
Application.CutCopyMode = False

DataRange.ClearContents
End If
Loop While Not c Is Nothing And c.Address <> firstAddress
End If

End Sub
 
P

Peruanos72

Thanks Joel. I've been able to work with what you gave me and i'm almost there.

Once i've pasted the data to the left I want to copy down from that first
cell so the data is changed to mirror the top cell and then erase the data in
that top cell.

Also, I deleted the code that clears the contents in the data range. I want
to keep that data and just modify what i've copied to the left.

Thanks again for your help.
 
J

Joel

Set LastCell = c.End(xlDown)
Set DataRange = Range(c, LastCell)
DataRange.Copy _
Destination:=c.Offset(0, -1)
c.copy destination:=Range(c,LastCell)
c.clearcontents.
 
P

Peruanos72

The copy down part works great. I just need to do the copy down step with the
data i pasted to the left and not on the original data range.
 
P

Peruanos72

Peruanos72 said:
The copy down part works great. I just need to do the copy down step with the
data i pasted to the left and not on the original data range.
 
Ad

Advertisements

P

Peruanos72

Hey Joel,

I wasn't sure if you were out of the office or if my last post wasn't clear.
I just need to do the copy down step with the data i pasted to the left and
not on the original data range. Can you still help with this? Thanks again!!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top