I have the following code

Public Sub test()
Dim cfind As Range
Dim x As Range
Dim cell As Range
Dim myrange As Range
Worksheets("printing").Activate
Set myrange = Range(Range("a3"), Cells(Rows.Count, 1).End(xlUp))
For Each cell In myrange

Worksheets("planning").Activate
With Range("e3:e61")
Set cfind = .Cells.Find(what:=cell.Value, lookat:=xlWhole)

Set x = cfind.End(xlToLeft)

End With
Worksheets("printing").Activate
cell.Offset(0, 3) = x
line20:
Next cell
End Sub

It almost does the job however if the value in in worksheet("printing") is
not found then it stops and shows an error. I would like it not to show an
error but return a blank value into the the worksheet

Why start a new thread? Stay with the old one...

--
Don Guillett
SalesAid Software

"Paul" wrote in message
...
I have the following code

Public Sub test()
Dim cfind As Range
Dim x As Range
Dim cell As Range
Dim myrange As Range
Worksheets("printing").Activate
Set myrange = Range(Range("a3"), Cells(Rows.Count, 1).End(xlUp))
For Each cell In myrange

Worksheets("planning").Activate
With Range("e3:e61")
Set cfind = .Cells.Find(what:=cell.Value, lookat:=xlWhole)

Set x = cfind.End(xlToLeft)

End With
Worksheets("printing").Activate
cell.Offset(0, 3) = x
line20:
Next cell
End Sub

It almost does the job however if the value in in worksheet("printing") is
not found then it stops and shows an error. I would like it not to show an
error but return a blank value into the the worksheet

'---------------------
Public Sub test()
Dim cfind As Range
Dim x As Variant
Dim cell As Range
Dim myrange As Range
Worksheets("printing").Activate
Set myrange = Range(Range("a3"), Cells(Rows.Count, 1).End(xlUp))
For Each cell In myrange

Worksheets("planning").Activate
With Range("e3:e61")
Set cfind = .Cells.Find(what:=cell.Value, lookat:=xlWhole)
If cfind Is Nothing Then
x = 0
Else
x = cfind.End(xlToLeft).Value
End If
End With
Worksheets("printing").Activate
cell.Offset(0, 3) = x
line20:
Next cell
End Sub
'---------------------

HTH
--
AP

"Paul" a écrit dans le message de
...
I have the following code

Public Sub test()
Dim cfind As Range
Dim x As Range
Dim cell As Range
Dim myrange As Range
Worksheets("printing").Activate
Set myrange = Range(Range("a3"), Cells(Rows.Count, 1).End(xlUp))
For Each cell In myrange

Worksheets("planning").Activate
With Range("e3:e61")
Set cfind = .Cells.Find(what:=cell.Value, lookat:=xlWhole)

Set x = cfind.End(xlToLeft)

End With
Worksheets("printing").Activate
cell.Offset(0, 3) = x
line20:
Next cell
End Sub

It almost does the job however if the value in in worksheet("printing") is
not found then it stops and shows an error. I would like it not to show an
error but return a blank value into the the worksheet

Thanks for your reply! I was wondering if you could help me further. I need
the formatting to also be copied, is this posible?

Change

cell.Offset(0, 3) = x


to
cfind.End(xlToLeft).copy cell.offset(0,3)

if the source cell has a formula and you want to do values and formats

cfind.End(xlToLeft).copy
With cell.offset(0,3)
.pastespecial xlValues
.pastespecial xlFormats
End With



--
Regards,
Tom Ogilvy


"Paul" wrote in message
...
Thanks for your reply! I was wondering if you could help me further. I
need
the formatting to also be copied, is this posible?

Answered in original thread.

--
Regards,
Tom Ogilvy


"Paul" wrote in message
...
I have the following code

Public Sub test()
Dim cfind As Range
Dim x As Range
Dim cell As Range
Dim myrange As Range
Worksheets("printing").Activate
Set myrange = Range(Range("a3"), Cells(Rows.Count, 1).End(xlUp))
For Each cell In myrange

Worksheets("planning").Activate
With Range("e3:e61")
Set cfind = .Cells.Find(what:=cell.Value, lookat:=xlWhole)

Set x = cfind.End(xlToLeft)

End With
Worksheets("printing").Activate
cell.Offset(0, 3) = x
line20:
Next cell
End Sub

It almost does the job however if the value in in worksheet("printing") is
not found then it stops and shows an error. I would like it not to show an
error but return a blank value into the the worksheet