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

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
 

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

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
 

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

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
 

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" wrote in message
oups.com...
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