text search plus other columns T/F search to produce result?

[Gingertrees]

Relevant columns in my sample spreadsheet:
Name.............../Sea-doo products/Hullman products
Joe's Appliance../FALSE/FALSE
Rapid Runners..../FALSE/TRUE
Watertown Boats/FALSE/FALSE
Miguel's Motors.../TRUE/TRUE
Virginia Marine.../FALSE/FALSE

I'm trying to fill in a column on a different sheet that would classify the business as "boating".
Three Criteria:
Name contains "Marin" "yacht" "boat" or "water"
OR
Sea-doo = true
OR
Hullman = true
My guess is a custom function, though I'm real bad at coding them. The text search REALLY throws me off. Ideas?

 

[Gingertrees]

Well I tried the following, which would be just the beginning statement, but it tripped the stupid "Out of Memory" error (using 2010).

Sub Macro3()
'
' Macro3 Macro
For i = 2 To 655
Application.EnableEvents = False
With Cells(i, 3)
    If Not InStr(Cells, "MARIN") = 1 Then
    MsgBox ("Nope")
    Else
    End If
End With
Next
Application.EnableEvents = True
End Sub

I am completely taking a shot in the dark here. I don't understand a darn thing about VBA, I guess at everything. Could someone with a clue please tell me if I'm on the right track and maybe what I'm doing wrong?????????????????

 

[Scott Huish]

Here you go:

Sub Test()
Dim c As Range
For Each c In Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row)
    If IsBoating(c.Text, c.Offset(, 1).Resize(, 2)) Then _
        Worksheets("Sheet2").Range(c.Address) = "boating"
Next
End Sub
 
Function IsBoating(r As String, twocols As Range) As Boolean
With CreateObject("vbscript.regexp")
    .Pattern = "Marin|yacht|boat|water"
    IsBoating = .Test(r) Or twocols(, 1) Or twocols(, 2)
End With
End Function

 

[Gingertrees]

Almost! Just some tweaking needed...

This 99% works for me - I altered the offsets in the function to reflect the location of the columns in question (business name = col. C, Seadoo = H, Hullman = J). One fix I need help on is where this places positive results ("boating") - it currently puts them in the c.Address column (col. C) that would otherwise hold the business name. I actually need that to go 6 columns to the right of the Address (col. I).

I tried the following, but it said "type mismatch" and said =False:

Sub Test()
Dim c As Range
For Each c In Range("C2:C" & Range("C" & Rows.Count).End(xlUp).Row)
    If IsBoating(c.Text, c.Offset(, 1).Resize(, 2)) Then _
        Worksheets("Sheet2").Range(c.Offset(, 6)) = "boating"
    '///quoth the debugger, " c.Offset(,6)=False "
Next
End Sub
 
Function IsBoating(r As String, twocols As Range) As Boolean
With CreateObject("vbscript.regexp")
    .Pattern = "marin|yacht|boat|water"
    IsBoating = .Test(r) Or twocols(, 5) Or twocols(, 7)
End With
End Function

does this have to do with the fact that the function is looking for values on Sheet1, and placing results in Sheet2?

 

[Scott Huish]

Sub Test()
Dim c As Range
For Each c In Range("C2:C" & Range("C" & Rows.Count).End(xlUp).Row)
    If IsBoating(c.Text, c.Offset(, 5), c.Offset(, 7)) Then _
        Worksheets("Sheet2").Range(c.Offset(, 6).Address) = "boating"
Next
End Sub
 
Function IsBoating(r As String, colone As Range, coltwo As Range) As Boolean
With CreateObject("vbscript.regexp")
    .Pattern = "marin|yacht|boat|water"
    .IgnoreCase = True
    IsBoating = .Test(r) Or colone Or coltwo
End With
End Function

 

[Gingertrees]

Hurrah! Thanks. BTW Hotpepper, adorable avatar

Here was the final code that worked:

Sub Test()
Dim c As Range
For Each c In Range("C2:C" & Range("C" & Rows.Count).End(xlUp).Row)
    If IsBoating(c.Text, c.Offset(, 5), c.Offset(, 7)) Then _
    Worksheets("Sheet2").Range(c.Offset(, 6).Address) = "boating"
    '  Then
        
Next
End Sub
 
Function IsBoating(r As String, colone As Range, coltwo As Range) As Boolean
' ) As Boolean
With CreateObject("vbscript.regexp")
    .Pattern = "marin|yacht|boat|water"
    .IgnoreCase = True
    IsBoating = .Test(r) Or colone = True Or coltwo = True
    
End With
End Function