0

Good day to everyone,

I have been trying to find an answer here that would fit my problem but I have been unsuccessful. I am using FIND to search column F for cell with #N/A value and copy adjacent cells to another "Sheet2" at the end of the column A. I have made the following code that works but my problem is I want to make it to loop to find the next cell with #N/A value till find all.

Sub Find()
    Dim SerchRange As Range
    Dim FindCell As Range
    Set SerchRange = Range("F:F")
    Set FindCell = SerchRange.FIND(What:="#N/A", _
      LookIn:=xlValues, _
      LookAt:=xlWhole, _
      SearchOrder:=xlByRows, _
      SearchDirection:=xlNext, _
      MatchCase:=False)

    If FindCell Is Nothing Then
        MsgBox "Nothing was found all clear"    
    Else
        FindCell.Select
        ActiveCell.Offset(0, -3).Resize(, 3).Select
        Selection.Copy
        Sheets("Sheet2").Select
        Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Select
        ActiveSheet.Paste
    End If
End Sub
Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Vaggelis
  • 57
  • 1
  • 7
  • 1
    Possible duplicate of [Find and FindNext for Excel VBA](https://stackoverflow.com/questions/30380490/find-and-findnext-for-excel-vba) – Pᴇʜ Nov 07 '18 at 09:05
  • Additionally I recommend to read [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) and apply this technique to your code. – Pᴇʜ Nov 07 '18 at 09:06

1 Answers1

1

Try this and let me know if it works:

Option Explicit
Sub Find()
Application.ScreenUpdating = False
Dim SearchRange As Range
Dim FindCell As Range
Dim Check As Boolean
Dim LastRow As Long
Dim ws As Worksheet
Dim ws2 As Worksheet
Dim FindCounter As Long

Set ws = ThisWorkbook.Worksheets("Sheet1") ' <--- Insert the name of your worksheet here
Set ws2 = ThisWorkbook.Worksheets("Sheet2")
LastRow = ws.Cells(Rows.Count, "F").End(xlUp).Row ' <--- Finds the last used row
Set SearchRange = Range("F1:F" & LastRow)
FindCounter = 0

For Each FindCell In SearchRange
    If FindCell.Value = "#N/A" Then
        FindCounter = FindCounter + 1
        FindCell.Offset(0, -3).Resize(, 3).Copy
        ws2.Range("A" & ws2.Cells(Rows.Count, "A").End(xlUp).Row + 1).PasteSpecial xlPasteValues
    End If
Next

MsgBox "Succes!" & vbNewLine & vbNewLine & "This many cells were found: " & FindCounter
Application.ScreenUpdating = True
End Sub
DirtyDeffy
  • 497
  • 7
  • 18
  • Thank you very much DirtyDeffy it works!!!!!! The only change I made is to remove “ThisWorbook.” from the two sets. As you understand I am not hard coder but I am trying, my thought was to target the cells with “#N/A” because these cells are 15 or 20 max in a range of 5000 and its time consuming to do it manually…As I understand your code search every cell to much the value “#N/A” am I right? – Vaggelis Nov 07 '18 at 12:47
  • @Vaggelis For a new coder, your idea was nicely thought out. Only a few places in the execution, where it needed improvement. `ThisWorkbook` simply specifies which workbook the code needs to target. Yes you are completely right. My version of the code is almost identical to the `.Find` method. My version uses a loop to go through column F and if the cell value is "#N/A" then it triggers the copy/paste. I would like you to thoroughly look through my code and ask me if there is anything you don't understand. You only get better at coding if you understand the code you're using :) – DirtyDeffy Nov 07 '18 at 13:27
  • @Vaggelis However in this particular case, using a filter instead of VBA coding might have been easier ;) – DirtyDeffy Nov 07 '18 at 13:28
  • DirtyDeffy I much appreciate your help, your code is very very good and yes I can read it little by little! I thing .Find because this code is a small part of bigger code. I work with huge excel spreadsheets and the code must be efficient, usually 150 000 lines and more! The big code copy data from other two excels in sheet 3 and 4 refresh pivot in sheet 1 now your code find the #N/A copy them to Sheet2 and I have code more to export a report and automate email…! I have many things to learn to code like you! I have many questions about other codes may I ask you about or I must upload question? – Vaggelis Nov 07 '18 at 14:28
  • @Vaggelis Post a new question and link it here as a comment :) – DirtyDeffy Nov 08 '18 at 08:33
  • Hi DirtyDeffy i have post another question, here is the link: https://stackoverflow.com/questions/53373236/copy-a-range-of-adjacent-columns-from-sheet1-to-sheet2 – Vaggelis Nov 19 '18 at 11:06
  • https://stackoverflow.com/questions/53866938/pivot-table-filter-month-select-all-before-current-month – Vaggelis Dec 20 '18 at 10:56
  • Hi DirtyDeffy i have a question about Pivot Table if you have time to help me with this thank you! – Vaggelis Dec 20 '18 at 10:58
  • I tried to run your code but it was giving me error 'If FindCell.Value = "#N/A" Then' and the error message was Type Mismatch. How can I fix it? Also, I wonder if F in this line for find or F column 'LastRow = ws.Cells(Rows.Count, "F").End(xlUp).Row' ? – Nena Apr 09 '22 at 18:03
  • Hi @Nena, 1) In Vaggelis case the "#N/A" values were strings with the literal value of #N/A and not an actual error. If you have actual #N/A errors you first need to check if `FindCell.Value` is an error and then check if the error is the type `CVErr(xlErrNA)`. You can do it this way: `If IsError(FindCell.Value) Then` `If FindCell.Value = CVErr(xlErrNA) Then` 2) The F you are asking about is refering to the column F. That specific line of code finds the row number of the last used cell in a given column – DirtyDeffy Apr 10 '22 at 20:25