Search again if not found

Question

So I have a part in my macro that I want to add what I assume needs to be an "Else" portion, but I am not that good with macros and am asking for help.

Range("Z1").Copy

Dim FindString As String
Dim Rng As Range
FindString = Sheets("Pull").Range("Y1").Value
If Trim(FindString) <> "" Then
    With Sheets("HourTracker").Range("A:A")
        Set Rng = .Find(What:=FindString, _
                        After:=.Cells(.Cells.Count), _
                        LookIn:=xlValues, _
                        LookAt:=xlWhole, _
                        SearchOrder:=xlByRows, _
                        SearchDirection:=xlNext, _
                        MatchCase:=False)
        If Not Rng Is Nothing Then
            Application.Goto Rng, True
        Else
            MsgBox "Nothing found"
        End If
    End With
            ActiveCell.Offset(0, 1).Activate
            Selection.PasteSpecial xlPasteValues
Application.DisplayAlerts = True
    End If

End Sub

So what I want this to do, is instead of "MsgBox "Nothing Found"", I want it to essentially perform the same thing as above, but copy cell Z2, and search for the value of Y2 in the same sheet "HourTracker" then paste the value. I have no idea on how to accomplish this, and all my attempts have failed. Any help would be much appreciated. Let me know if you need more clarification, thank you in advance!!!

score 0 · Accepted Answer · answered May 28 '17 at 01:36

Sounds to me like you're looking for a loop.

Sub findStuff()

Application.DisplayAlerts = False

' The item you want to paste
Dim PasteString As String

' The item you're looking for
Dim FindString As String

' The range that may containing FindString
Dim Rng As Range

' The variable used to loop through your range
Dim iCounter as Long

' loop through the first cell in column Y to the last used cell
For iCounter = 1 To Sheets("Pull").Cells(Rows.Count, 25).End(xlUp).Row

    ' PasteString = the current cell in column Z
    PasteString = Sheets("Pull").Cells(iCounter, 26).Value

    ' FindString = the current cell in column Y
    FindString = Sheets("Pull").Cells(iCounter, 25).Value


    If Trim(FindString) <> "" Then
        With Sheets("HourTracker").Range("A:A")

            ' Find the cell containing FindString
            Set Rng = .Find(What:=FindString, _
                            After:=.Cells(.Cells.Count), _
                            LookIn:=xlValues, _
                            LookAt:=xlWhole, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False)

            If Not Rng Is Nothing Then
                ' There's no need to activate/ select the cell.
                ' You can directly set the value with .Value
                Rng.Offset(0, 1).Value = PasteString
            Else
                ' Do nothing
            End If

        End With
    Else
        ' Do nothing
    End If

Next

Application.DisplayAlerts = True

End Sub

Every time the compiler hits Next it will start again at For but raise the value of iCounter by 1. We can use Cells to accomplish this since Cells takes the row and column arguments as numbers, not strings (like Range). The syntax is simply Cells(Row #, Column #). Therefore, every time the For . . . Next loops around again, iCounter will go up by one and you'll search in the next row.

Instead of using .Paste, you can set the value of a cell directly with .Value. Pasting is pretty slow and using .Value is much faster.

Cells().End(xlUp).Row is a method used to find the last used cell in a range. See Error in finding last used cell in VBA for a much better explanation than I can give here.

Search again if not found

1 Answers1