add to an existing function

[Guest]

Hello,

I need to add an if statement to existing cells that already contain
formulas. For example, the cells contain a =vlookup(...) statement, but
because I'm getting some #n/a, I need to add an if statement such as
=if(iserror(vlookup(...)),"No Data", vlookup(...)) to all the cells. The
problem is that I have over 5000 cells with formulas in it, and I just need
to add the if statement!

Is there a way to do this instead of having to go into every single cell and
re-enter the additional statements?

Thanks in advance

 

[Arvi Laanemets]

Hi

An example

In Cell C2 you have a formula
=IF(ISERROR(VLOOKUP($A2,Sheet1!$A$2:$X$100,6,0)),"No
Data",VLOOKUP($A2,Sheet1!$A$2:$X$100,6,0))

When you copy C2 down, then in C3 the formula will search for value equal to
one in A3, in C4 for value equal yo one in A4, etc, in range
Sheet1!$A$2:$X$100. I.e you edit the formula in one cell, and copy it to
other cells in same column.

The character $ (or absence of it) determines refernce type, i.e. how the
reference behaves when copied. With $ the reference is absolute, without it
relative. There are 4 possible variations
$C$R - the reference remains same when copied
C$R - the row reference reamains same, column reference changes
$CR - the row reference changes, column reference reamains same
CR - both row and column references change.

Arvi Laanemets

 

[Guest]

Hi Arvi,

Thanks for the reply. I was not specific in my previous message. I don't
have the same vlookup in all the cells, so I cannot simply drag the cell that
I changed to the adjacent cells. I have vlookups in cells but doing
different things. All I need is to add an if statement so that when I get
#n/a, I will have text instead of the error.

 

[Gord Dibben]

Nelson

Sub NATrapAdd()
Dim myStr As String
Dim cel As Range
For Each cel In Selection
If cel.HasFormula = True Then
If Not cel.Formula Like "=IF(ISNA*" Then
myStr = Right(cel.Formula, Len(cel.Formula) - 1)
cel.Value = "=IF(ISNA(" & myStr & "),""""," & myStr & ")"
'for text, change """" to "text message"
End If
End If
Next
End Sub


Gord Dibben Excel MVP

 

[Guest]

Thank you...it was what I was looking for!

Nelson

Sub NATrapAdd()
Dim myStr As String
Dim cel As Range
For Each cel In Selection
If cel.HasFormula = True Then
If Not cel.Formula Like "=IF(ISNA*" Then
myStr = Right(cel.Formula, Len(cel.Formula) - 1)
cel.Value = "=IF(ISNA(" & myStr & "),""""," & myStr & ")"
'for text, change """" to "text message"
End If
End If
Next
End Sub


Gord Dibben Excel MVP

 

[Gord Dibben]

Thanks for the feedback.

Gord

Thank you...it was what I was looking for!