assume the data is in a single column


Dim rng as Range
On Error Resume Next
set rng = Columns(3).SpecialCells(xlFormulas,xlErrors)
On Error Goto 0
if not rng is nothing then
rng.Formula = "=" & rng(1).Offset(-1,0).Address(0,0)

for each ar in rng.Areas
ar.Formula = ar.Value
Next
End if


--
Regards,
Tom Ogilvy



"Barclay0x00" wrote in message
oups.com...
What I a trying to do:
Given a range of cells, some of which contain errors (e.g. #DIV/0),
replace each cell having an error with the value of the last cell in
the range that is
a) not an error &
b) is before the current cell

Example:
Input: 2.05 #DIV/0 #DIV/0 2.55 #DIV/0
Return: 2.05 2.05 2.05 2.55 2.55

Attempt at Code:
Function SuperASP(ASP As Range) As Range
Dim Size As Integer
Dim Size2 As Integer
Size = WorksheetFunction.CountA(ASP) - 1
Size2 = Size
For i = Size To 0 Step -1
If Not WorksheetFunction.IsError(ASP(i)) Then
For j = Size2 To i Step -1
SuperASP.Cells(i).Value = ASP.Cells(i).Value
Next j
Size2 = i
End If
Next i
End Function

Current Input: ={SuperASP(B5:M5)}
Current Output: #Value is returned for each cell in the range

As is obvious from the code, I am misunderstanding quite a bit. Any
corrections to the code, or suggestions on a better to do this are
appreciated.

Thank you,
Barclay