Dim rng as Range, cell as Range, cell1 as Range
Dim rng2 as Range, c as Long, cell2 as Range

with worksheets("sheet1") 'or whatever sheet your data is on
set rng2 = .Range(.Cells(2,"D"),.Cells(2,"D").End(xldown))
end with

c = Application.Calculation
Application.Calculation = xlManual

for each cell2 in rng2
if abs(100-cell2) < .00001 then
'typo alert next line <----
set rng = cell2.EntireRow.SpecialCells(xlFormulas,xlNumbers)
for each cell in rng
if cell.column < 5 and cell.column < 8 then
set cell1 = Worksheets("Sheet2").Range(cell.Address)
cell.copy
cell1.PasteSpecial xlValues
cell1.PasteSpecial xlFormats
end if
Next
end if
Next
Application.Calculation = c


(untested, but it did compile)


bobby wrote:

Hi tom,

The compiler is giving code error. it is telling that .cells as
invalid reference.

in the line

setrng2 = range(.cells(2,"D"),.cells(2,"D").end(xldown))

can you look at that.

Thanks & Regards

Ramana

--

Dave Peterson