ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   On error goto specified Line (https://www.excelbanter.com/excel-programming/423425-error-goto-specified-line.html)

Peruanos72
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

joel
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

Peruanos72
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

joel
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

Peruanos72
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

Peruanos72
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

Peruanos72
On error goto specified Line
 
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!!



"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


All times are GMT +1. The time now is 07:52 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com