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

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


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

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.

"Arvi Laanemets" wrote:

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


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

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

On Wed, 7 Dec 2005 12:20:01 -0800, Nelson
wrote:

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.

"Arvi Laanemets" wrote:

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


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

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

"Gord Dibben" wrote:

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

On Wed, 7 Dec 2005 12:20:01 -0800, Nelson
wrote:

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.

"Arvi Laanemets" wrote:

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


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

Thanks for the feedback.

Gord

On Wed, 7 Dec 2005 14:06:03 -0800, Nelson
wrote:

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

"Gord Dibben" wrote:

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

On Wed, 7 Dec 2005 12:20:01 -0800, Nelson
wrote:

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.

"Arvi Laanemets" wrote:

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


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