Find, Copy, Paste from one sheet to another Loop and If not found, Insert text into cell

Question

I have the following code that Loops and searches through a range of sheets and copies and pastes to a "Blast List" sheet as it finds the correct values:

Sub CopySingle()

    Dim wsfr As Worksheet
    Dim wsl As Worksheet
    Dim BlNumber As String
    Dim BSStep As Long

    Dim SI As String
    Dim Srng As Range
    Dim Nrng As Range

    Dim Rrng As Range
    Dim Brng As Range

    Dim Arng As Range

    Application.ScreenUpdating = False

    BSStep = 1

    Set Rrng = ThisWorkbook.Worksheets("Blast List").Range("A3", Range("A3").End(xlDown))

    Set Srng = ThisWorkbook.Worksheets("Blast List").Range("E1:Q1")

    For Each Brng In Rrng.Cells

        For Each Nrng In Srng.Cells

        On Error Resume Next

        SI = Nrng.Value

        BlNumber = CStr("Blasted " & BSStep)

        Set wsfr = ThisWorkbook.Worksheets(CStr(BlNumber))
        Set wsl = ThisWorkbook.Worksheets("Blast List")

        wsfr.Select
            Range("A1").Select
                Cells.Find(What:=SI, LookIn:=xlFormulas, _
                LookAt:=xlPart, SearchOrder:=xlByRows, _
                SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate
                Selection.Copy

        Sheets("Blast List").Select
            Range("A1").Select
                Cells.Find(What:=BlNumber, LookIn:=xlFormulas, _
                LookAt:=xlPart, SearchOrder:=xlByRows, _
                SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).End(xlToRight).Offset(0, 1).Select

                Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                :=False, Transpose:=False

        Next Nrng

        BSStep = BSStep + 1

    Next Brng

    Application.ScreenUpdating = True

End Sub

I am now trying to figure out how to adapt the code that if the value is not found, it will put the text "NO INFORMATION" in red in the cell.

Any and all help appreciated.

Regards

I think you should study some of your previous answers and get in the habit of avoiding Select and using Find properly (to avoid having to use OERN). — SJR, Oct 14 '19 at 11:07
@SJR, I am trying to learn VBA and I am doing so systematically. I will in future learning attempts attempt to follow your advice. — Hendrik Sidaway, Oct 14 '19 at 11:14
It will make your life a lot easier. If you assign the result of Find to a variable you can check if it's nothing (i.e. value not found). And this is how to avoid Select https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba — SJR, Oct 14 '19 at 11:18
@SJR thank you I did see this in my previous question and will work through it in due time. I am learning to crawl before I walk if I could put it that way, as I am first trying to logically go about this step by step. — Hendrik Sidaway, Oct 14 '19 at 11:39
So are you saying that if the first Find doesn't find anything the code should put NO INFO in the result of the second FIND? — SJR, Oct 14 '19 at 12:00
@SJR, No, What I am saying is if the first find does not find the specified string, it should put a NO INFO in the cell and continue the loop. But only the first find, not the second — Hendrik Sidaway, Oct 14 '19 at 13:10
@SJR I am starting over with the entire macro as I want to eliminate all select — Hendrik Sidaway, Oct 16 '19 at 06:32

Find, Copy, Paste from one sheet to another Loop and If not found, Insert text into cell

0 Answers0