help with loops in code,

[Guest]

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

 

[Don Guillett]

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.

 

[Tim Williams]

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

 

[Tom Ogilvy]

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