PDA

View Full Version : Reference #N/A



Lartk
04-03-2013, 10:31 AM
A formula is used in cell G2. If the formula doesnt work, the cell has #N/A N/A

Is there a way to reference those cells in which I get an #N/A N/A?

JKwan
04-03-2013, 11:50 AM
try using

IsError()

mdmackillop
04-03-2013, 12:27 PM
Not clear if you want VBA or Worksheet reference
=IF(ISNA(H8),"Error","")
If Application.IsNA([H8]) Then MsgBox "Error"

Lartk
04-03-2013, 12:30 PM
Sub PriceFormula()
Dim NumBlocks As Long
Dim Lastrow As Long
Dim i As Long
With ActiveSheet

Lastrow = .Cells(.Rows.Count, "F").End(xlUp).Row
For i = 3 To Lastrow

If .Cells(i, "G").Value = "IsError()" Then

.Cells(i, "G").Formula = _
"=BDH(RC[-5]&"" equity"",""px_last"")"

End If

Next i

End With
End Sub



I am trying to say if the cells in column G have an error, then enter the formula above..

JKwan
04-03-2013, 12:41 PM
Try this

Sub PriceFormula()
Dim NumBlocks As Long
Dim Lastrow As Long
Dim i As Long
With ActiveSheet

Lastrow = .Cells(.Rows.Count, "F").End(xlUp).Row
For i = 3 To Lastrow
If Application.IsNA(.Cells(i, "G").Value) Then
.Cells(i, "G").Formula = _
"=BDH(RC[-5]&"" equity"",""px_last"")"
End If

Next i

End With
End Sub