View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.programming
Paul Paul is offline
external usenet poster
 
Posts: 661
Default help with loops in code,

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