Dim rng as Range
Dim rng1 as Range
Dim i as long
Dim j as Long
set rng = Range("AA74:AA100").specialCells(xlConstants,xlNum bers)
set rng1 = Intersect(rng.Entirerow,Columns(26))
i = 0
j = 3
for each cell in rng1
i = i + 1
j = j + 1
cells(70,i).Value = cell.Value
if i = 10 then exit for
Next


If the numbers in AA are produced by formulas, then change xlConstants to
xlformulas

--
Regards,
Tom Ogilvy


Richard wrote in message
...
Could anyone help me with this please.
I have a Target range of C70 to L70 (10 cells).

How do I copy the numbers in ColumnZ which are adjacent to
the corresponding numbers in ColumnAA in the table below
to the Range C70 to L70. Sometimes after I run my macro,
there are more or less numbers in ColumnAA and I just want
to copy up to 10 numbers but only those from columnZ that
have a number in the same row beside them.

In my table, I need to copy 10,2,4,7,9,8,5 and 6, (a total
of 8 numbers in this case) to cells commencing with C70
across. Cells K70 and L70 would be blank in this example.
If there are more than 10 rows that have data in both Z
and AA the ones over 10 are ignored.

Row ColZ ColAA
74 10 4.7
75 2 5.1
76 4 6.6
77 7 8.2
78 9 12.7
79 8 17.4
80 5 20.9
81 6 21.2
82 11
83 12
84 13
85 14
86 15
87 16
88 17
89 18
90 19
91 20
92 21
93 22
94 23
95 24
96 25
97 26