Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
#Value! error when evaluating data in a cell range | Excel Worksheet Functions | |||
Double clicking formula cell doesn't highlight precedent cells | Setting up and Configuration of Excel | |||
How to have an error popup once range cell amount is greater than | Excel Discussion (Misc queries) | |||
Help with this error Unknown reason for error with Range().Select | Excel Programming | |||
Type Mismatch error & subscript out of range error | Excel Programming |