Recongnizing #N/A as cell contents

[MichaelRobert]

I am writing a macro that will look at the contents of cell b3, which is
populated from another worksheet. This second worksheet returns to b3 a Part
Number if one exists, or #N/A if no Part Number exists.

I want my macro to look at b3. If it sees "#N/A", I want to launch a Message
Box that if clicked will generate a Part Number. I am OK on the message box,
but I am unable to construct the needed IF function without generating
compile errors. Her is what I have currently:

Sub ClickHere()
If Range("b3") = "#N/A" Then MsgBox "OK"
End Sub

[The "OK" in the MsgBox is my placeholder for more code ...]

Any solutions please?

Thanks.

Mike

 

[Chip Pearson]

You can use code like the following:

If IsError(Range("B3").Value) = True Then
If Range("B3").Value = CVErr(xlErrNA) Then
Debug.Print "N/A error"
Else
Debug.Print "Some other error, not #N/A"
End If
Else
Debug.Print "Not an error"
End If

Cordially,
Chip Pearson
Microsoft MVP
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[MichaelRobert]

Chip:

Thanks for the speedy reply. Does the code that you suggest open a Message
Box when cell b3 contains "#N/A"? That's what I want to do, but from my very
little knowledge of VBA, I don't see how the code has that as an output.

Again, thanks ...

Mike

You can use code like the following:

If IsError(Range("B3").Value) = True Then
If Range("B3").Value = CVErr(xlErrNA) Then
Debug.Print "N/A error"
Else
Debug.Print "Some other error, not #N/A"
End If
Else
Debug.Print "Not an error"
End If

Cordially,
Chip Pearson
Microsoft MVP
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

I am writing a macro that will look at the contents of cell b3, which is
populated from another worksheet. This second worksheet returns to b3 a Part
Number if one exists, or #N/A if no Part Number exists.

I want my macro to look at b3. If it sees "#N/A", I want to launch a Message
Box that if clicked will generate a Part Number. I am OK on the message box,
but I am unable to construct the needed IF function without generating
compile errors. Her is what I have currently:

Sub ClickHere()
If Range("b3") = "#N/A" Then MsgBox "OK"
End Sub

[The "OK" in the MsgBox is my placeholder for more code ...]

Any solutions please?

Thanks.

Mike

 

[Chip Pearson]

Mike,

No, the code writes a debug message to the Immediate window. Press
CTRL G to display the Immediate window if it is not visible.

Change "Debug.Print" to "MsgBox" if you want to display a message box.
E.g.,

MsgBox "Cell Has N/A Error"

Cordially,
Chip Pearson
Microsoft MVP
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

Chip:

Thanks for the speedy reply. Does the code that you suggest open a Message
Box when cell b3 contains "#N/A"? That's what I want to do, but from my very
little knowledge of VBA, I don't see how the code has that as an output.

Again, thanks ...

Mike

You can use code like the following:

If IsError(Range("B3").Value) = True Then
If Range("B3").Value = CVErr(xlErrNA) Then
Debug.Print "N/A error"
Else
Debug.Print "Some other error, not #N/A"
End If
Else
Debug.Print "Not an error"
End If

Cordially,
Chip Pearson
Microsoft MVP
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

I am writing a macro that will look at the contents of cell b3, which is
populated from another worksheet. This second worksheet returns to b3 a Part
Number if one exists, or #N/A if no Part Number exists.

I want my macro to look at b3. If it sees "#N/A", I want to launch a Message
Box that if clicked will generate a Part Number. I am OK on the message box,
but I am unable to construct the needed IF function without generating
compile errors. Her is what I have currently:

Sub ClickHere()
If Range("b3") = "#N/A" Then MsgBox "OK"
End Sub

[The "OK" in the MsgBox is my placeholder for more code ...]

Any solutions please?

Thanks.

Mike

 

[MichaelRobert]

Chip:

Thanks. That'll do it!

Mike

Mike,

No, the code writes a debug message to the Immediate window. Press
CTRL G to display the Immediate window if it is not visible.

Change "Debug.Print" to "MsgBox" if you want to display a message box.
E.g.,

MsgBox "Cell Has N/A Error"

Cordially,
Chip Pearson
Microsoft MVP
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

Chip:

Thanks for the speedy reply. Does the code that you suggest open a Message
Box when cell b3 contains "#N/A"? That's what I want to do, but from my very
little knowledge of VBA, I don't see how the code has that as an output.

Again, thanks ...

Mike

You can use code like the following:

If IsError(Range("B3").Value) = True Then
If Range("B3").Value = CVErr(xlErrNA) Then
Debug.Print "N/A error"
Else
Debug.Print "Some other error, not #N/A"
End If
Else
Debug.Print "Not an error"
End If

Cordially,
Chip Pearson
Microsoft MVP
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)



On Fri, 28 Nov 2008 12:27:01 -0800, MichaelRobert

I am writing a macro that will look at the contents of cell b3, which is
populated from another worksheet. This second worksheet returns to b3 a Part
Number if one exists, or #N/A if no Part Number exists.

I want my macro to look at b3. If it sees "#N/A", I want to launch a Message
Box that if clicked will generate a Part Number. I am OK on the message box,
but I am unable to construct the needed IF function without generating
compile errors. Her is what I have currently:

Sub ClickHere()
If Range("b3") = "#N/A" Then MsgBox "OK"
End Sub

[The "OK" in the MsgBox is my placeholder for more code ...]

Any solutions please?

Thanks.

Mike