hi,

I have the below macro which is almost there.

I need to stop the loop when the immediate cell above no longe
displays the result of the formula. I don't know how to test for thi
criteria.

thanks

Sub test()
Do
If IsEmpty(ActiveCell) Then
Selection.FormulaArray = _

"=IF(ROW()-ROW(NoBlanksRange)+1ROWS(BlanksRange)-COUNTBLANK(BlanksRange),"""",INDIRECT(ADDRESS(SMAL L((IF(BlanksRange<"""",ROW(BlanksRange),ROW()+ROW S(BlanksRange))),ROW()-ROW(NoBlanksRange)+1),COLUMN(BlanksRange),4)))"
If ActiveCell.Offset(-2, 0).Value = "" An
ActiveCell.Offset(-1, 0).Value = "" Then Exit Sub
End If
ActiveCell.Offset(1, 0).Select

Loop Until IsNull(ActiveCell.Offset(-1, 0))



End Su

--
Message posted from http://www.ExcelForum.com

Possibly

Sub test()
Do
If IsEmpty(ActiveCell) Then
Selection.FormulaArray = _

"=IF(ROW()-ROW(NoBlanksRange)+1ROWS(BlanksRange)-COUNTBLANK(BlanksRange),""
"",INDIRECT(ADDRESS(SMALL((IF(BlanksRange<"""",RO W(BlanksRange),ROW()+ROWS(
BlanksRange))),ROW()-ROW(NoBlanksRange)+1),COLUMN(BlanksRange),4)))"
End If
ActiveCell.Offset(1, 0).Select

Loop Until ActiveCell.Offset(-1, 0) = ""




"rbekka33 " wrote in message
...
hi,

I have the below macro which is almost there.

I need to stop the loop when the immediate cell above no longer
displays the result of the formula. I don't know how to test for this
criteria.

thanks

Sub test()
Do
If IsEmpty(ActiveCell) Then
Selection.FormulaArray = _


"=IF(ROW()-ROW(NoBlanksRange)+1ROWS(BlanksRange)-COUNTBLANK(BlanksRange),""
"",INDIRECT(ADDRESS(SMALL((IF(BlanksRange<"""",RO W(BlanksRange),ROW()+ROWS(
BlanksRange))),ROW()-ROW(NoBlanksRange)+1),COLUMN(BlanksRange),4)))"
If ActiveCell.Offset(-2, 0).Value = "" And
ActiveCell.Offset(-1, 0).Value = "" Then Exit Sub
End If
ActiveCell.Offset(1, 0).Select

Loop Until IsNull(ActiveCell.Offset(-1, 0))



End Sub


---
Message posted from http://www.ExcelForum.com/