Option Explicit
Sub Flasche()
Dim rng As Range, c As Range
Set rng = Sheets("Getränke").Range("W10:W23").SpecialCells(xlCellTypeConstants)
Set c = rng.Find("Flasche", , xlValues, xlWhole)
If Not c Is Nothing Then
Call MsgBox("Hit at " & c.Address(0, 0), vbInformation, "Flasche")
Else
Call MsgBox("no Hit", vbExclamation, "Flasche")
End If
End Sub
Sub DieFlasche()
Dim i As Long
For i = 10 To 16
If Not Sheets("Getränke").Cells(i, 23).HasFormula And _
Sheets("Getränke").Cells(i, 23).Value = "Flasche" Then
MsgBox "OK"
End If
Next i
End Sub
Sub Flaschenpost()
Dim arr() As Variant, x As Long
arr = Sheets("Getränke").Range("W10:W23").Value
For x = LBound(arr, 1) To UBound(arr, 1)
If arr(x, 1) = "Flasche" Then MsgBox "Hit"
Next x
End Sub