Need assistance with this find...

[mikekolba]

Trying to look at an offset value in my sheet when searching a column and find text in that offset location and if it matches then do an substitute operation on the current cell

Sub GoDaddy_Change_Taxable_to_Nontaxable_for_Services()
Dim cell As Range
Dim Offset As Range
Dim Find As Boolean
' Parse the offset GoDaddy Category for Training, Warranty or Service and Replace Taxable with No if matched
For Each cell In Selection

If Not IsEmpty(cell.Value) Then

If cell.Offset(0, -15).cell.find("Train" Or "Service" Or "Warrant", xlValues, xlPart, False) = True Then

cell.Value = WorksheetFunction.Substitute(cell, cell.Value, "No")

Else
End If

End If

Next cell
End Sub

for some reason though it's not offsetting and it's giving me a type mismatch at the red line.

 

[mikekolba]

Ok made the following modifications. Could still use some help as I'm getting run time error 91 on the line indicated in red.

Some help would be welcome. Please. I've spent most of today working on this one...

Sub GoDaddy_Change_Taxable_to_Nontaxable_for_Services()
Dim cell As Range
Dim Offset As Range
Dim Find As Boolean
Dim Lookup As Range
' Parse the offset GoDaddy Category for Training, Warranty or Service and Replace Taxable with No if matched
For Each cell In Selection

If Not IsEmpty(cell.Value) Then
If Lookup.Offset(0, -15).Range(cell).Find("Train", Lookup.Offset(0, -15).Range(cell), xlValues, xlPart, False) = True Then
cell.Value = WorksheetFunction.Substitute(cell, cell.Value, "No")

ElseIf Lookup.Offset(0, -15).Range(cell).Find("Service", Lookup.Offset(0, -15).Range(cell), xlValues, xlPart, False) = True Then
cell.Value = WorksheetFunction.Substitute(cell, cell.Value, "No")

ElseIf Lookup.Offset(0, -15).Range(cell).Find("Warrant", Lookup.Offset(0, -15).Range(cell), xlValues, xlPart, False) = True Then

cell.Value = WorksheetFunction.Substitute(cell, cell.Value, "No")
Else
End If

End If

Next cell
End Sub

 

[mikekolba]

Anyone out there?

 

[shg]

It would help if you would explain your data layout and describe the logic you're trying to apply.

 

[mikekolba]

basically I have 1 column of data selected where in the column, I am looking up offset data in an adjacent column in the same row if there is certain text contained in the data found there and if there is, I want to then replace the data in the original selected column

selected column is text and the offset column is also text. The text being searched for is a partial match to the whole text data in the offset column.

Thanks for clarifying on the additional data that you need to help me.

 

[shg]

Maybe like this:

Sub GoDaddy_Change_Taxable_to_Nontaxable_for_Services()
    Dim cell        As Range
    Dim s           As String
 
    For Each cell In Selection.Cells
        If VarType(cell.Value) = vbString Then
            s = cell.Offset(0, -15).Value
            If InStr(1, s, "train", vbTextCompare) > 0 Or _
               InStr(1, s, "service", vbTextCompare) > 0 Or _
               InStr(1, s, "warrant", vbTextCompare) > 0 Then
                cell.Value = "No"
            End If
        End If
    Next cell
End Sub

 

[mikekolba]

Thanks SHG. That worked like a champ.

Appreciate the help.

 

[shg]

Glad to hear it, you're welcome.

A suggestion for the future: Rather than just posting code that doesn't work, explain in plain, clear language what you're trying to do. It's a lot easier to help than by trying to divine the intent of the code.

 

[mikekolba]

SHG, I do have a question. I reused the same code for a different set of columns of data but it doesn't appear to parse the data and make the changes that I need.

Is there instances where that macro code won't work and what would cause it to not work? case sensitivity?

It doesn't error out but it runs very quickly almost like it's not returing vbstring for the vartype?

 

[mikekolba]

Anyone else have any ideas? It is returning the proper data type for the string (8) for vbstring?