Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying and Pasting Formula
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 ( ) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying and Pasting Formula
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" wrote in message om... 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 ( ) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying and Pasting Formula
Thanks so much for your help on this one.
Cheers - Al. "shockley" wrote in message ... 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" wrote in message om... 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 ( ) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I delete the refernce a formula when copying and pasting? | Excel Worksheet Functions | |||
copying and pasting a formula | Excel Worksheet Functions | |||
Trouble copying and pasting a formula | Excel Discussion (Misc queries) | |||
Copying & Pasting Formula | Excel Discussion (Misc queries) | |||
Trouble copying and pasting a formula | Excel Worksheet Functions |