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