Given a range of cells, some of which contain errors (e.g. #DIV/0), replace each cell having an error with the value of a non-error precedent cell

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

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" <[email protected]> wrote in message
news:[email protected]...
> 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
>

Tom,

Thanks for the immediate reply! I must be misunderstanding how to use
your code as a function.
The variable declaration "Dim rng As Range" gets replaced by:
Function MyFunction(rng as Range) As Range

I tried adding a line after the End If:
MyFunction = rng.Areas

but it returns #Value.

I apologize for being so helpless.

Thank you,
Barclay

If you wanted to use it as a function

public Function MyFunction(rng as Range)
Dim ar 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
MyFunction = True
Else
MyFunction = False
End If

end Function

You can only call this function from VBA. You can't use it as a UDF in a
worksheet. And it would only work with a range that is a single column. It
could certainly be fleshed out to be more robust.

--
Regards,
Tom Ogilvy



"Barclay0x00" <[email protected]> wrote in message
news:[email protected]...
> Tom,
>
> Thanks for the immediate reply! I must be misunderstanding how to use
> your code as a function.
> The variable declaration "Dim rng As Range" gets replaced by:
> Function MyFunction(rng as Range) As Range
>
> I tried adding a line after the End If:
> MyFunction = rng.Areas
>
> but it returns #Value.
>
> I apologize for being so helpless.
>
> Thank you,
> Barclay
>