| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Recongnizing #N/A as cell contents
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Recongnizing #N/A as cell contents
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 wrote: 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Recongnizing #N/A as cell contents
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" wrote: 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 wrote: 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Recongnizing #N/A as cell contents
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) On Fri, 28 Nov 2008 14:01:00 -0800, MichaelRobert wrote: 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" wrote: 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 wrote: 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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Recongnizing #N/A as cell contents
Chip:
Thanks. That'll do it! Mike "Chip Pearson" wrote: 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) On Fri, 28 Nov 2008 14:01:00 -0800, MichaelRobert wrote: 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" wrote: 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 wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| concatenate contents of cells whose contents resemble cell referem | Excel Worksheet Functions | |||
| Change contents of a cell based on cell contents. | Excel Programming | |||
| Macro to clear range contents when cell contents are changed by us | Excel Programming | |||
| Macro to remove contents of cell and move all other contents up one row | Excel Discussion (Misc queries) | |||
| Copying cell contents to add to existing contents in another cell | Excel Programming | |||
Hybrid Mode
