Simple Code Help

[DaveB]

Can you please help me with some code:

If in columns 1-3000, there is a sentence with "INC" in
it, then in column 2 the word "CORP" should appear.

If in columns 1-3000, there is a sentence with "SOCIETY"
in it, then in column 2 the word "ASSC/FNDN" should appear.

If in columns 1-3000, there is a sentence
with "FOUNDATION" in it, then in column 2 the
word "ASSC/FNDN" should appear.

If in columns 1-3000, there is a sentence
with "UNIVERSITY" in it, then in column 2 the word "ACAD"
should appear.

Thanks. DaveB

 

[Guest]

DaveB,

Try this:

Sub temp()
Dim i As Integer
Dim t As String

Sheets("Sheet1").Activate
For i = 1 To 3000
t = Cells(i, 1).Value
If InStr(1, t, " INC", vbTextCompare) > 0 Then
Cells(i, 2) = "CORP"
ElseIf InStr(1, t, "SOCIETY", vbTextCompare) > 0 Then
Cells(i, 2) = "ASSC/FNDN"
ElseIf InStr(1, t, "FOUNDATION", vbTextCompare) > 0 Then
Cells(i, 2) = "ASSC/FNDN"
End If
Next i

End Sub


Three things to note:

First, in the IF with INC - I used " INC" so that "SINCE" wouldn't trigger it.

Second, add an ELSE and blank out column 2 if you'd like. As it is if you change a value in column 1 and re-run it, old values may not be replaced correctly.

Third, I used vbTextCompare so that it would not be case sensitive.

Art