Recongnizing #N/A as cell contents

M

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
 
C

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)
 
M

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

Chip Pearson said:
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
Click to expand...
Click to expand...
 
C

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

Chip Pearson said:
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
Click to expand...
Click to expand...
Click to expand...
 
M

MichaelRobert

Chip:

Thanks. That'll do it!

Mike

Chip Pearson said:
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

Chip Pearson said:
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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Running a Macro when cell value is a specified value 3
How to stop 'Egg Timer' 3
Trying to run excel vba code when cell value changes from empty to entered value. 1
programme run help 2
Find #N/A in column D 2
cell contents in a text/message box 1
running a programme 9
Pasting cell contents into a macro 1

Top