Copying and Pasting Formula

[Al Mackay]

Would anyone be able to offer advise on how to do the following?

Need to be able to paste a formula =IF(COUNTBLANK(C12:H12)<6,"Y","")
into a range in a worksheet ("B12:B161") - obviously each row that it
goes down to the formula would be slightly different, e.g. Row 13
would mean the formula would change to reflect C13:H13.

The only problem is that I don't want Excel to paste this formula in
if any of the cells in the range contain a "N" or "No" so I would want
it to loop through the cells copying and pasting the formulas in each
cell (within the range) where they don't contain the "N" or "No".

As always your advise and time is really appreciated.

Many Thanks - Al Mackay ( (e-mail address removed) )

 

[shockley]

There is probably a more elegant way of doing this, but this is my brute
force method:

Sub Tester()
Set rng = Range(Cells(12, 2), Cells(161, 2))
For Each cell In rng
If cell <> "N" And cell <> "No" Then
cell.Formula = "=IF(COUNTBLANK(C" _
& cell.Row & ":H" & cell.Row & _
")<6," & Chr(34) & "Y" & Chr(34) _
& "," & Chr(34) & Chr(34) & ")"
End If
Next
End Sub


HTH,
Shockley

 

[Al Mackay]

Thanks so much for your help on this one.

Cheers - Al.