VBA- if criteria met, place text in the next column

[HockeyDiablo]

I would like to do an IF formula, but with VBA

I have information in Column"Q" "Alphanumeric string 255 max char" with note based descriptions
I have a blank Column"R"

Formula in "R2" =IF(ISNUMBER(SEARCH(" sc ",Q2)),"Service Call","") **Id like to do multiple conditions: SC or sc or S/C or s/c

I have about 39,000 rows to do this with, which is why I would like to try in VBA.

What if I needed a different instance?

=IF(ISNUMBER(SEARCH(" sp ",Q2)),"Springs","") **Multiple conditions...
=IF(ISNUMBER(SEARCH(" Amarr",Q2)),"Door","") **Multiple conditions...


​Thanks

 

[Special-K99]

What if sc is at the end or the beginning of Q2 ? Searching for " sc " won't find it

Here's what you should do

Add a space at the beginning and end of what you're searching for.
Then search for " sc "
So that should be
=IF(ISNUMBER(SEARCH(" sc "," "&Q2&" ")),"Service Call","")

Ultimately for VBA you should:
1) Store what your'e searching (Q2) in a variable
2) Insert a space at the beginning and end
3) Change it to all Upper Case
4) Replace all "/"s with nulls ("")
5) Now you need search for just " SC " - You don't need to asearch for SC or sc or S/C or s/c

 

[HockeyDiablo]

What if sc is at the end or the beginning of Q2 ? Searching for " sc " won't find it

Here's what you should do

Add a space at the beginning and end of what you're searching for.
Then search for " sc "
So that should be
=IF(ISNUMBER(SEARCH(" sc "," "&Q2&" ")),"Service Call","")

Ultimately for VBA you should:
1) Store what your'e searching (Q2) in a variable
2) Insert a space at the beginning and end
3) Change it to all Upper Case
4) Replace all "/"s with nulls ("")
5) Now you need search for just " SC " - You don't need to asearch for SC or sc or S/C or s/c

Thanks to Chris Slade on stackoverflow:

<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; white-space: inherit;">Sub ColQtoColR()
Dim FirstRow, LastRow, RowCount As Long
Dim GCell As Range

FirstRow = 2
LastRow = 39000
For RowCount = FirstRow To LastRow
Set GCell = Cells(RowCount, 17)
If InStr(GCell, "sc") = 1 Then
GCell.Offset(0, 1).Value = "Service Call"
ElseIf InStr(GCell, "sp") = 1 Then
GCell.Offset(0, 1).Value = "Springs"
ElseIf InStr(GCell, "Amarr") = 1 Then
GCell.Offset(0, 1).Value = "Door"
Else
GCell.Offset(0, 1).Value = ""
End If
Next RowCount

End Sub</code>