continuing saga of worksheets printing and planning

I have 2 worksheets printing and planning
printing extends from a1:ae35
planning extends from a1:bh61

I have the following code that works perfectly for a range e3:e61 But when
I come to change it to another range it does not work....

I want it to extend the range to check for values in columns g, i, k
........ (every second column until bg) in planning and return results in
columns d, e, f, g etc to column ae

I still only want to compare values in column A in sh.printing and still
return the corresponding value in column A in sh.planning.

I ve spent hours fiddling around with loops and things, but no luck.

Here is the code that uses range e3:e61:

Public Sub test2()
Dim cfind As Range
Dim x As Range
Dim cell As Range
Dim myrange As Range
With Worksheets("printing")
Set myrange = .Range(.Range("a3"), .Cells(Rows.Count, 1).End(xlUp))
End With
For Each cell In myrange
With Worksheets("planning").Range("e3:e61")
Set cfind = .Cells.Find(what:=cell.Value, lookat:=xlWhole)
If Not cfind Is Nothing Then
Set x = .Cells(cfind.Row, 1)
Worksheets("printing").Activate
cfind.End(xlToLeft).Copy cell.Offset(0, 3)
Else
cell.Offset(0, 3).Value = ""
End If
End With
Next cell
End Sub

Thanks in advance

Paul

I can't visualize what you want so you may send a workbook to MY email below
along another sheet showing what the desired results would be. Copy THIS
email to another sheet in the file.

--
Don Guillett
SalesAid Software


"Paul" wrote in message
...
continuing saga of worksheets printing and planning

I have 2 worksheets printing and planning
printing extends from a1:ae35
planning extends from a1:bh61

I have the following code that works perfectly for a range e3:e61 But
when
I come to change it to another range it does not work....

I want it to extend the range to check for values in columns g, i, k
....... (every second column until bg) in planning and return results in
columns d, e, f, g etc to column ae

I still only want to compare values in column A in sh.printing and still
return the corresponding value in column A in sh.planning.

I ve spent hours fiddling around with loops and things, but no luck.

Here is the code that uses range e3:e61:

Public Sub test2()
Dim cfind As Range
Dim x As Range
Dim cell As Range
Dim myrange As Range
With Worksheets("printing")
Set myrange = .Range(.Range("a3"), .Cells(Rows.Count, 1).End(xlUp))
End With
For Each cell In myrange
With Worksheets("planning").Range("e3:e61")
Set cfind = .Cells.Find(what:=cell.Value, lookat:=xlWhole)
If Not cfind Is Nothing Then
Set x = .Cells(cfind.Row, 1)
Worksheets("printing").Activate
cfind.End(xlToLeft).Copy cell.Offset(0, 3)
Else
cell.Offset(0, 3).Value = ""
End If
End With
Next cell
End Sub

Thanks in advance

Paul

Paul,

One clarification:
cfind.End(xlToLeft).Copy cell.Offset(0, 3)

Where does the xlToLeft take you? Should this always go to column A?

Tim


"Paul" wrote in message ...
continuing saga of worksheets printing and planning

I have 2 worksheets printing and planning
printing extends from a1:ae35
planning extends from a1:bh61

I have the following code that works perfectly for a range e3:e61 But when
I come to change it to another range it does not work....

I want it to extend the range to check for values in columns g, i, k
....... (every second column until bg) in planning and return results in
columns d, e, f, g etc to column ae

I still only want to compare values in column A in sh.printing and still
return the corresponding value in column A in sh.planning.

I ve spent hours fiddling around with loops and things, but no luck.

Here is the code that uses range e3:e61:

Public Sub test2()
Dim cfind As Range
Dim x As Range
Dim cell As Range
Dim myrange As Range
With Worksheets("printing")
Set myrange = .Range(.Range("a3"), .Cells(Rows.Count, 1).End(xlUp))
End With
For Each cell In myrange
With Worksheets("planning").Range("e3:e61")
Set cfind = .Cells.Find(what:=cell.Value, lookat:=xlWhole)
If Not cfind Is Nothing Then
Set x = .Cells(cfind.Row, 1)
Worksheets("printing").Activate
cfind.End(xlToLeft).Copy cell.Offset(0, 3)
Else
cell.Offset(0, 3).Value = ""
End If
End With
Next cell
End Sub

Thanks in advance

Paul

Public Sub test2()
Dim cfind As Range
Dim rng1 As Range
Dim cell As Range
Dim myrange As Range
Dim icol As Long, i As Long
With Worksheets("printing")
Set myrange = .Range(.Range("a3"), .Cells(Rows.Count, 1).End(xlUp))
End With
With Worksheets("planning")
Set rng1 = .Range("e3")
For i = 7 To 59 Step 2
Set rng1 = Union(rng1, .Cells(3, i))
Next
Set rng1 = Intersect(rng1.EntireColumn, _
.Range("3:61").EntireRow)
End With
For Each cell In myrange
With rng1
Set cfind = .Cells.Find(what:=cell.Value, lookat:=xlWhole)
If Not cfind Is Nothing Then
icol = ((cfind.Column - 3) / 2) + 3
rng1.Parent.Cells(cfind.Row, icol).Copy _
cell.Parent.Cells(cell.Row, "AE")
End If
Else
cell.Offset(0, 3).Value = ""
End If
End With
Next cell
End Sub

--
Regards,
Tom Ogilvy

"Paul" wrote in message
...
continuing saga of worksheets printing and planning

I have 2 worksheets printing and planning
printing extends from a1:ae35
planning extends from a1:bh61

I have the following code that works perfectly for a range e3:e61 But
when
I come to change it to another range it does not work....

I want it to extend the range to check for values in columns g, i, k
....... (every second column until bg) in planning and return results in
columns d, e, f, g etc to column ae

I still only want to compare values in column A in sh.printing and still
return the corresponding value in column A in sh.planning.

I ve spent hours fiddling around with loops and things, but no luck.

Here is the code that uses range e3:e61:

Public Sub test2()
Dim cfind As Range
Dim x As Range
Dim cell As Range
Dim myrange As Range
With Worksheets("printing")
Set myrange = .Range(.Range("a3"), .Cells(Rows.Count, 1).End(xlUp))
End With
For Each cell In myrange
With Worksheets("planning").Range("e3:e61")
Set cfind = .Cells.Find(what:=cell.Value, lookat:=xlWhole)
If Not cfind Is Nothing Then
Set x = .Cells(cfind.Row, 1)
Worksheets("printing").Activate
cfind.End(xlToLeft).Copy cell.Offset(0, 3)
Else
cell.Offset(0, 3).Value = ""
End If
End With
Next cell
End Sub

Thanks in advance

Paul