If #N/A in Col. AB, copy/paste Items in Col C. to New Sheet
 

Struggling with the code below:
Sub CopyData10()
Dim rng As Range, cell As Range
Dim rw As Long
Set rng = Worksheets("diego").Range("AB2:AB630")
rw = 1
For Each cell In rng
If cell.Value = "#N/A" Then
Worksheets("Summary Sheet").Cells(rw, "C") = cell.Offset(0, -1)
rw = rw + 1
End If
Next
End Sub

The code fails on this line. i guess there is not an equivalent of #N/A in
VBA. What do I need to use? By the way, the #N/A is the result of a
function; it is not hard-coded.

Thanks,
Ryan---


--
RyGuy

If #N/A in Col. AB, copy/paste Items in Col C. to New Sheet
 

Try this:

Sub CopyData10()
Dim rng As Range, cell As Range
Dim rw As Long
Set rng = Worksheets("diego").Range("AB2:AB630")
rw = 1
For Each cell In rng
If IsError(cell) Then
Worksheets("Summary Sheet").Cells(rw, 3) = cell.Offset(0, -1)
rw = rw + 1
End If
Next
End Sub
--
CFS


"ryguy7272" wrote:

Struggling with the code below:
Sub CopyData10()
Dim rng As Range, cell As Range
Dim rw As Long
Set rng = Worksheets("diego").Range("AB2:AB630")
rw = 1
For Each cell In rng
If cell.Value = "#N/A" Then
Worksheets("Summary Sheet").Cells(rw, "C") = cell.Offset(0, -1)
rw = rw + 1
End If
Next
End Sub

The code fails on this line. i guess there is not an equivalent of #N/A in
VBA. What do I need to use? By the way, the #N/A is the result of a
function; it is not hard-coded.

Thanks,
Ryan---


--
RyGuy

If #N/A in Col. AB, copy/paste Items in Col C. to New Sheet
 

My guess: a cell my DISPLAY #N/A but it may STORE a formula such as
=IF(A10,A1, NA())
The trick is to have VBA know that you do not mean: does the cell have the
text "#N/A" ?
This user-defined function tell if mycell is displaying #N/A or not

Function trythis(mycell)
If Application.WorksheetFunction.IsNA(mycell) Then
trythis = "Has NA"
Else
trythis = "Not NA"
End If
End Function

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"ryguy7272" wrote in message
...
Struggling with the code below:
Sub CopyData10()
Dim rng As Range, cell As Range
Dim rw As Long
Set rng = Worksheets("diego").Range("AB2:AB630")
rw = 1
For Each cell In rng
If cell.Value = "#N/A" Then
Worksheets("Summary Sheet").Cells(rw, "C") = cell.Offset(0, -1)
rw = rw + 1
End If
Next
End Sub

The code fails on this line. i guess there is not an equivalent of #N/A
in
VBA. What do I need to use? By the way, the #N/A is the result of a
function; it is not hard-coded.

Thanks,
Ryan---


--
RyGuy

If #N/A in Col. AB, copy/paste Items in Col C. to New Sheet
 

Thanks for the look guys. CFS, it didn't work because the #N/A is the result
of a Vlookup(). It's because of what Bernard said. I guess VBA is looking
at the cell as if a user hit Alt+~. The #N/A is just the result of the
Vlookup function not finding a match. How can I modify the IF statement to
accommodate this?

Thanks,
Ryan---

--
RyGuy


"Bernard Liengme" wrote:

My guess: a cell my DISPLAY #N/A but it may STORE a formula such as
=IF(A10,A1, NA())
The trick is to have VBA know that you do not mean: does the cell have the
text "#N/A" ?
This user-defined function tell if mycell is displaying #N/A or not

Function trythis(mycell)
If Application.WorksheetFunction.IsNA(mycell) Then
trythis = "Has NA"
Else
trythis = "Not NA"
End If
End Function

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"ryguy7272" wrote in message
...
Struggling with the code below:
Sub CopyData10()
Dim rng As Range, cell As Range
Dim rw As Long
Set rng = Worksheets("diego").Range("AB2:AB630")
rw = 1
For Each cell In rng
If cell.Value = "#N/A" Then
Worksheets("Summary Sheet").Cells(rw, "C") = cell.Offset(0, -1)
rw = rw + 1
End If
Next
End Sub

The code fails on this line. i guess there is not an equivalent of #N/A
in
VBA. What do I need to use? By the way, the #N/A is the result of a
function; it is not hard-coded.

Thanks,
Ryan---


--
RyGuy

If #N/A in Col. AB, copy/paste Items in Col C. to New Sheet
 

I made a test using Vlookup() and the macro ran properly... The VBA function
IsError() can identify this kind of error.
--
CFS


"ryguy7272" wrote:

Thanks for the look guys. CFS, it didn't work because the #N/A is the result
of a Vlookup(). It's because of what Bernard said. I guess VBA is looking
at the cell as if a user hit Alt+~. The #N/A is just the result of the
Vlookup function not finding a match. How can I modify the IF statement to
accommodate this?

Thanks,
Ryan---

--
RyGuy


"Bernard Liengme" wrote:

My guess: a cell my DISPLAY #N/A but it may STORE a formula such as
=IF(A10,A1, NA())
The trick is to have VBA know that you do not mean: does the cell have the
text "#N/A" ?
This user-defined function tell if mycell is displaying #N/A or not

Function trythis(mycell)
If Application.WorksheetFunction.IsNA(mycell) Then
trythis = "Has NA"
Else
trythis = "Not NA"
End If
End Function

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"ryguy7272" wrote in message
...
Struggling with the code below:
Sub CopyData10()
Dim rng As Range, cell As Range
Dim rw As Long
Set rng = Worksheets("diego").Range("AB2:AB630")
rw = 1
For Each cell In rng
If cell.Value = "#N/A" Then
Worksheets("Summary Sheet").Cells(rw, "C") = cell.Offset(0, -1)
rw = rw + 1
End If
Next
End Sub

The code fails on this line. i guess there is not an equivalent of #N/A
in
VBA. What do I need to use? By the way, the #N/A is the result of a
function; it is not hard-coded.

Thanks,
Ryan---


--
RyGuy

If #N/A in Col. AB, copy/paste Items in Col C. to New Sheet
 

My UDF works when #N/A is the result of a VLOOKUP formula. Have you tried
it.
In A10 I have a VLOOKUP returning #N?A in B10 I have =trythis(A10) and it
returns "Has N/A"

Have you replaced the code in your macro: If cell.Value = "#N/A" Then
by : If Application.WorksheetFunction.IsNA(cell) Then

What does "CFS" refer to?
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"ryguy7272" wrote in message
...
Thanks for the look guys. CFS, it didn't work because the #N/A is the
result
of a Vlookup(). It's because of what Bernard said. I guess VBA is
looking
at the cell as if a user hit Alt+~. The #N/A is just the result of the
Vlookup function not finding a match. How can I modify the IF statement
to
accommodate this?

Thanks,
Ryan---

--
RyGuy


"Bernard Liengme" wrote:

My guess: a cell my DISPLAY #N/A but it may STORE a formula such as
=IF(A10,A1, NA())
The trick is to have VBA know that you do not mean: does the cell have
the
text "#N/A" ?
This user-defined function tell if mycell is displaying #N/A or not

Function trythis(mycell)
If Application.WorksheetFunction.IsNA(mycell) Then
trythis = "Has NA"
Else
trythis = "Not NA"
End If
End Function

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"ryguy7272" wrote in message
...
Struggling with the code below:
Sub CopyData10()
Dim rng As Range, cell As Range
Dim rw As Long
Set rng = Worksheets("diego").Range("AB2:AB630")
rw = 1
For Each cell In rng
If cell.Value = "#N/A" Then
Worksheets("Summary Sheet").Cells(rw, "C") = cell.Offset(0, -1)
rw = rw + 1
End If
Next
End Sub

The code fails on this line. i guess there is not an equivalent of
#N/A
in
VBA. What do I need to use? By the way, the #N/A is the result of a
function; it is not hard-coded.

Thanks,
Ryan---


--
RyGuy

If #N/A in Col. AB, copy/paste Items in Col C. to New Sheet
 

Sorry for being so thick!! Overwhelmed with tasks today!!
I needed NOT!!!

If Not IsError(cell) Then

Works perfect, CFS and Bernard!


Thanks,
Ryan---

--
RyGuy


"Bernard Liengme" wrote:

My UDF works when #N/A is the result of a VLOOKUP formula. Have you tried
it.
In A10 I have a VLOOKUP returning #N?A in B10 I have =trythis(A10) and it
returns "Has N/A"

Have you replaced the code in your macro: If cell.Value = "#N/A" Then
by : If Application.WorksheetFunction.IsNA(cell) Then

What does "CFS" refer to?
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"ryguy7272" wrote in message
...
Thanks for the look guys. CFS, it didn't work because the #N/A is the
result
of a Vlookup(). It's because of what Bernard said. I guess VBA is
looking
at the cell as if a user hit Alt+~. The #N/A is just the result of the
Vlookup function not finding a match. How can I modify the IF statement
to
accommodate this?

Thanks,
Ryan---

--
RyGuy


"Bernard Liengme" wrote:

My guess: a cell my DISPLAY #N/A but it may STORE a formula such as
=IF(A10,A1, NA())
The trick is to have VBA know that you do not mean: does the cell have
the
text "#N/A" ?
This user-defined function tell if mycell is displaying #N/A or not

Function trythis(mycell)
If Application.WorksheetFunction.IsNA(mycell) Then
trythis = "Has NA"
Else
trythis = "Not NA"
End If
End Function

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"ryguy7272" wrote in message
...
Struggling with the code below:
Sub CopyData10()
Dim rng As Range, cell As Range
Dim rw As Long
Set rng = Worksheets("diego").Range("AB2:AB630")
rw = 1
For Each cell In rng
If cell.Value = "#N/A" Then
Worksheets("Summary Sheet").Cells(rw, "C") = cell.Offset(0, -1)
rw = rw + 1
End If
Next
End Sub

The code fails on this line. i guess there is not an equivalent of
#N/A
in
VBA. What do I need to use? By the way, the #N/A is the result of a
function; it is not hard-coded.

Thanks,
Ryan---


--
RyGuy

If #N/A in Col. AB, copy/paste Items in Col C. to New Sheet
 

If Application.WorksheetFunction.IsNA(mycell) Then

Just for the record, another way is

If mycell.Value = CVErr(xlErrNA) Then
Debug.Print "N/A error"

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)


On Thu, 22 Jan 2009 13:24:06 -0400, "Bernard Liengme"
wrote:

My guess: a cell my DISPLAY #N/A but it may STORE a formula such as
=IF(A10,A1, NA())
The trick is to have VBA know that you do not mean: does the cell have the
text "#N/A" ?
This user-defined function tell if mycell is displaying #N/A or not

Function trythis(mycell)
If Application.WorksheetFunction.IsNA(mycell) Then
trythis = "Has NA"
Else
trythis = "Not NA"
End If
End Function

best wishes

If #N/A in Col. AB, copy/paste Items in Col C. to New Sheet
 

Thanks Chip! You know, I Googled around a bit before posting. I saw a
sample that used your technique, but I couldn't get that working...thus the
post.

Thanks to everyone who participated!
Ryan---



--
RyGuy


"Chip Pearson" wrote:


If Application.WorksheetFunction.IsNA(mycell) Then

Just for the record, another way is

If mycell.Value = CVErr(xlErrNA) Then
Debug.Print "N/A error"

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)


On Thu, 22 Jan 2009 13:24:06 -0400, "Bernard Liengme"
wrote:

My guess: a cell my DISPLAY #N/A but it may STORE a formula such as
=IF(A10,A1, NA())
The trick is to have VBA know that you do not mean: does the cell have the
text "#N/A" ?
This user-defined function tell if mycell is displaying #N/A or not

Function trythis(mycell)
If Application.WorksheetFunction.IsNA(mycell) Then
trythis = "Has NA"
Else
trythis = "Not NA"
End If
End Function

best wishes