PDA

View Full Version : [SOLVED] Look for #N/A sign then copy paste



Nicolaf
01-23-2014, 07:29 AM
Hi,

I have data in two columns in Sheet1.

In column A between A1 and A20 I have a list of names.
In column B I have a VLOOKUP function (in cells B1 to B20) which looks for name in column A and returns a value.

I would like a macro that looks for all the #N/A in column B (cells B1 to B20) and if it finds #N/A then it will copy name in corresponding cell A into Sheet2 at the end of list of names in column B (let's suppose the names start from cell B1 Sheet2 going down).

How do I do that?

:think: :doh::think:

Nix

Bob Phillips
01-23-2014, 08:06 AM
Public Sub PostErrorDetails()

With Worksheets("Sheet1")

.Range("A1:B1").AutoFilter
.Range("$A$1:$B$20").AutoFilter Field:=2, Criteria1:="#N/A"
.Range("A1:A20").Copy Worksheets("Sheet2").Range("A1")
.Range("A1").AutoFilter
End With

Worksheets("Sheet2").Select
End Sub

Nicolaf
01-30-2014, 04:48 AM
Great thanks!
Nic
:hi::hi: