Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
FIND and REPLACE characters needed
What characters (~, #, ?) do I use to Find and Replace item cells of first
following row with second row below? FIND these types, eg: =(DSUM(DIVOH,$CX$1,$DG$2:$DG$20) Replace with these types, eg: =IF(ISERROR(DSUM(DIVOH,$CX$1,$DG$2:$DG$20)),0) Thanks. Peter C |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
FIND and REPLACE characters needed
Peter,
In general, first find and replace = with '= Then F&R =DSUM with =IF(ISERROR(DSUM Then F&R ) with )),0) Then F&R '= with = Of course, =IF(ISERROR(DSUM(DIVOH,$CX$1,$DG$2:$DG$20)),0) isn't a good formula, so that isn't what you really want to do. Better would be a macro: Select all your cells with formulas and run this macro: Sub MakeIfIserror() Dim myCell As Range Dim myForm As String For Each myCell In Selection.SpecialCells(xlCellTypeFormulas) myForm = Mid(myCell.Formula, 2, Len(myCell.Formula)) myCell.Formula = "=IF(ISERROR(" & myForm & "),0," & myForm & ")" Next myCell End Sub HTH, Bernie MS Excel MVP "Peter C" wrote in message ... What characters (~, #, ?) do I use to Find and Replace item cells of first following row with second row below? FIND these types, eg: =(DSUM(DIVOH,$CX$1,$DG$2:$DG$20) Replace with these types, eg: =IF(ISERROR(DSUM(DIVOH,$CX$1,$DG$2:$DG$20)),0) Thanks. Peter C |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
FIND and REPLACE characters needed
A big thanks Bernie, that was excellent!!!
"Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Peter, In general, first find and replace = with '= Then F&R =DSUM with =IF(ISERROR(DSUM Then F&R ) with )),0) Then F&R '= with = Of course, =IF(ISERROR(DSUM(DIVOH,$CX$1,$DG$2:$DG$20)),0) isn't a good formula, so that isn't what you really want to do. Better would be a macro: Select all your cells with formulas and run this macro: Sub MakeIfIserror() Dim myCell As Range Dim myForm As String For Each myCell In Selection.SpecialCells(xlCellTypeFormulas) myForm = Mid(myCell.Formula, 2, Len(myCell.Formula)) myCell.Formula = "=IF(ISERROR(" & myForm & "),0," & myForm & ")" Next myCell End Sub HTH, Bernie MS Excel MVP "Peter C" wrote in message ... What characters (~, #, ?) do I use to Find and Replace item cells of first following row with second row below? FIND these types, eg: =(DSUM(DIVOH,$CX$1,$DG$2:$DG$20) Replace with these types, eg: =IF(ISERROR(DSUM(DIVOH,$CX$1,$DG$2:$DG$20)),0) Thanks. Peter C |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
FIND and REPLACE characters needed | Excel Worksheet Functions | |||
How do I find replace special characters? | Excel Discussion (Misc queries) | |||
How can I replace 2 of 5 characters within an cell in MS Excel? | Excel Worksheet Functions | |||
Find and replace unusual characters ... | Excel Discussion (Misc queries) | |||
VB Find and Replace | Excel Worksheet Functions |