Switch possible?

[Gil149]

I am using the code below to find something and offset the data. I am looking to change it a bit though...if the FindValue is not found I want to to find the last row and paste it one row lower. I can do the offsetting...I am stumped on the switching it to if it's not found, versus is found...thanks guys!

        For Each rngcell In Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row)
            FindValue = rngcell.Offset(0, 0).Value
                With Sheets("Resources").Range("A2:A" & Sheets("Resources").Range("A" & Rows.Count).End(xlUp).Row)
                    Set Rng = .Find(What:=FindValue, After:=.Cells(.Cells.Count), LookIn:=xlValues, LookAt:= _
                        xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
                    If Not Rng Is Nothing Then
                        FirstAddress = Rng.Address
                        Do
                            Rng.Row.Copy
                            Sheets("Resources").Range("A2:A" & Sheets("Resources").Range("A" & Rows.Count).End(xlUp).Row)(1).Paste
                            Set Rng = .FindNext(Rng)
                        Loop While Not Rng Is Nothing And Rng.Address <> FirstAddress
                    End If
                End With
        Next rngcell

 

[Jerry Sullivan]

You can add an Else statement to your code to do something when no match for FindValue found.

If Not Rng Is Nothing Then
   '-----do this if at least one instance of FindValue found
        FirstAddress = Rng.Address
        Do
           Rng.Row.Copy '???
           Sheets("Resources").Range("A2:A" & Sheets("Resources").Range("A" & Rows.Count).End(xlUp).Row)(1).Paste
            Set Rng = .FindNext(Rng)
        Loop While Not Rng Is Nothing And Rng.Address <> FirstAddress
Else
    '-----do this if no instances of FindValue found
'Your code here
End If

The code in red doesn't look correct.
Rng.Row will return a Row Number and you probably intend that to be a Range object such as Rng.EntireRow.

Once you have the Row copied, do you want to Paste it at the Top or Bottom?