Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > On error goto specified Line
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 58
Default On error goto specified Line
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
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 9,101
Default On error goto specified Line
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



"Peruanos72" wrote:

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
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 58
Default On error goto specified Line
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.

"Joel" wrote:

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



"Peruanos72" wrote:

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
Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 9,101
Default On error goto specified Line
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.


"Peruanos72" wrote:

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.

"Joel" wrote:

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



"Peruanos72" wrote:

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
Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 58
Default On error goto specified Line
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.

"Joel" wrote:

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.


"Peruanos72" wrote:

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.

"Joel" wrote:

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



"Peruanos72" wrote:

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
Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 58
Default On error goto specified Line


"Peruanos72" wrote:

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.

"Joel" wrote:

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.


"Peruanos72" wrote:

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.

"Joel" wrote:

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



"Peruanos72" wrote:

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
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Hybrid Mode Hybrid Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
goto next line Dave Peterson Excel Programming 0 September 17th 08 01:45 PM
advanced filter, goto next line(record) Pierre[_18_] Excel Programming 6 December 20th 07 06:14 PM
Error Handling - On Error GoTo doesn't trap error successfully David Excel Programming 9 February 16th 06 05:59 PM
On Error Goto doesn't goto Paul Excel Programming 1 October 15th 04 03:51 PM
On Error Goto doesn't goto Paul Excel Programming 0 October 15th 04 03:05 PM


All times are GMT +1. The time now is 03:02 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement