how to search for a string using vba

[sweetness34]

Hey guys, I got a few lines of code that work pretty well with one small problem. What I'm doing is searching my spreadsheet for "apples" (now I know apples is not in my data but I need the computer to understand that its ok if its not) but, correct me if I'm wrong. what I think it's doing is since it's not there it's giving me an error because im telling it to select something that is not existant. Now what can I change the .Select to in order for it to search but not try and select since its not there?
heres my code

Sheets("data").Select
If Cells.Find(What:="apples", SearchDirection:=xlNext, LookAt:=xlWhole).[COLOR=red]Select[/COLOR] Then
Cells.Find(What:="apples", After:=Range("A1"), SearchDirection:=xlPrevious).Select
z2 = Selection.Offset(1, 6).Address
Range(z2).Select
Selection.copy
Sheets("Report").Select
Range("B5").Select
    Selection.PasteSpecial paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
Else
End If

Thanks

 

[Scott Huish]

You get rid of Select.

It is very rare that you need to Select anything in VBA. You can work with ranges directly.

Dim a variable as a range. I am going to use c. Then refer to this range variable:

Dim c As Range
Set c = If Cells.Find(What:="apples", SearchDirection:=xlNext, LookAt:=xlWhole).Select Then
Cells.Find(What:="apples", After:=Range("A1"), SearchDirection:=xlPrevious)
If Not c Is Nothing Then....

 

[Scott Huish]

The next part is also too much code for what it does.

Dim z2 as a range, and then use it as a Range.

Using our c variable from before:

Set z2 = Selection.Offset(1, 6)
z2.Copy
Sheets("Report").Range("B5").PasteSpecial paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

 

[sweetness34]

You get rid of Select.

It is very rare that you need to Select anything in VBA. You can work with ranges directly.

Dim a variable as a range. I am going to use c. Then refer to this range variable:

Dim c As Range
Set c = If Cells.Find(What:="apples", SearchDirection:=xlNext, LookAt:=xlWhole).Select Then
Cells.Find(What:="apples", After:=Range("A1"), SearchDirection:=xlPrevious)
If Not c Is Nothing Then....

it wont let me use this code for some reason. It just turns red

 

[AlphaFrog]

Maybe try something like this...

Sub Find_Last_Apple()
    Dim FoundApples As Range
    Set FoundApples = Sheets("data").Cells.Find(What:="apples", _
                                                SearchDirection:=xlPrevious, _
                                                LookAt:=xlWhole, _
                                                MatchCase:=False)
    If FoundApples Is Nothing Then
        MsgBox "No apples found."
    Else
        Sheets("Report").Range("B5").Value = FoundApples.Offset(1, 6).Value
    End If
End Sub

 

[Scott Huish]

You were supposed to finish the Then statement in my example.

 

[sweetness34]

Thank you guys so much!!


HOTPEPPER- I cant honestly remember if i did or not haha.. its monday

 

[sweetness34]

Maybe try something like this...

Sub Find_Last_Apple()
    Dim FoundApples As Range
    Set FoundApples = Sheets("data").Cells.Find(What:="apples", _
                                                SearchDirection:=xlPrevious, _
                                                LookAt:=xlWhole, _
                                                MatchCase:=False)
    If FoundApples Is Nothing Then
        MsgBox "No apples found."
    Else
        Sheets("Report").Range("B5").Value = FoundApples.Offset(1, 6).Value
    End If
End Sub

is there an easy way to make this so it finds the row with "apples" in column C then "Red" in column D?

 

[AlphaFrog]

Sub Find_Apple_Red()

    Dim Found As Range, RedApple As Range, FirstFound As String
    
    Set Found = Sheets("data").Range("C:C").Find(What:="Apples", _
                                                 LookIn:=xlValues, _
                                                 LookAt:=xlWhole, _
                                                 SearchDirection:=xlNext, _
                                                 MatchCase:=False)
                                                
    If Found Is Nothing Then
        MsgBox "No apples found in column C."
        Exit Sub
    Else
        FirstFound = Found.Address
        Do
            If LCase(Found.Offset(, 1).Value) = "red" Then Set RedApple = Found.Resize(, 2)
            Set Found = Sheets("data").Range("C:C").FindNext(After:=Found)
        Loop Until Found.Address = FirstFound Or Not RedApple Is Nothing
    End If
    
    If Not RedApple Is Nothing Then
        RedApple.Select
        MsgBox "Red Apples found in " & RedApple.Address
    Else
        MsgBox "Red Apples not found."
    End If
    
End Sub

 

[sweetness34]

Sub Find_Apple_Red()
 
    Dim Found As Range, RedApple As Range, FirstFound As String
 
    Set Found = Sheets("data").Range("C:C").Find(What:="Apples", _
                                                 LookIn:=xlValues, _
                                                 LookAt:=xlWhole, _
                                                 SearchDirection:=xlNext, _
                                                 MatchCase:=False)
 
    If Found Is Nothing Then
        MsgBox "No apples found in column C."
        Exit Sub
    Else
        FirstFound = Found.Address
        Do
            If [COLOR=red]LCase[/COLOR](Found.Offset(, 1).Value) = "red" Then Set RedApple = Found.Resize(, 2)
            Set Found = Sheets("data").Range("C:C").FindNext(After:=Found)
        Loop Until Found.Address = FirstFound Or Not RedApple Is Nothing
    End If
 
    If Not RedApple Is Nothing Then
        RedApple.Select
        MsgBox "Red Apples found in " & RedApple.Address
    Else
        MsgBox "Red Apples not found."
    End If
 
End Sub

I noticed that you put LCase. How can I change this so its not case sensitive?