Searching for #NA within a range

Resolved · Medium Priority · Version 2007

Tue 22 Nov 2011, 17:05 Delegate Ryan said...

Ryan has attended:
Excel Advanced course
Excel VBA Intro Intermediate course

Searching for #NA within a range

Good afternoon,

Please can you help? I am trying to write a vba code to search in column D for values that have returned an #N/A value from a vlookup function and copy the data from columns B and C to another location.

Kind thanks,

Ryan

Fri 25 Nov 2011, 11:59 Trainer Simon said...

RE: Searching for #NA within a range

Hi Ryan,

Thank you for your question and welcome to the forum.

The following code will find and select all the cells that contain an error which include #N/A. Then you can tag on and write a small bit of code to copy the results to a new sheet.

Option Explicit
Sub foo()
Dim rStart As Range, rEnd As Range, c As Range
Set rStart = [A1]
Set rEnd = rStart.SpecialCells(xlCellTypeLastCell)

For Each c In Range(rStart, rEnd)
If IsError(c) Then
Debug.Print c.Address, c.Text ' replace this with copy or move
End If
Next c

End Sub

You will need to add to this code by adding variables that hold the values in col c and d and for the cells where the data is to be copied to.

I hope this helps.

Regards

Simon

Wed 30 Nov 2011, 16:04 Delegate Ryan said...

RE: Searching for #NA within a range

Hi Simon,

Thank you for your help. Although my end code did end up slightly different, your answer was certainly very helpful! My code ended up as follows:

Sub Fixna()

Dim intRow
Dim intLastRow
Dim numrows As Integer
Dim accname
Dim clientname As String

intLastRow = Range("B65536").End(xlUp).Row

For intRow = intLastRow To 1 Step -1

Rows(intRow).Select
accname = Cells(intRow, 4)
If IsError(accname) Then
ActiveSheet.Range(Cells(intRow, 2), Cells(intRow, 3)).Select
Selection.Copy
Windows("Client" & " " & "List" & ".xls").Activate
ActiveSheet.Range("a1").End(xlDown).Offset(1, 0).Select
ActiveCell.PasteSpecial
ActiveCell.Offset(0, 1).Select
clientname = ActiveCell
ActiveCell.Offset(0, 1).Select
Application.ScreenUpdating = True
ActiveCell = InputBox("SAGE REF FOR" & " " & clientname & "is...", "ENTER SAGE REF")
Application.ScreenUpdating = False
Windows("Daybook " & PeriodTwo & " " & PeriodOne & ".csv").Activate
End If

Next intRow

Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveSheet.Paste
Application.CutCopyMode = False
End Sub