Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Searching for #NA within a range
|
|
|
The UK's most regular Training information: |
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Searching for #NA within a range
Resolved · Medium Priority · Version 2007
Reply Tue 22 Nov 2011, 17:05Delegate 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
For upcoming training course dates see: Pricing & availability
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
Reply Wed 30 Nov 2011, 16:04Delegate 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
|