PDA

View Full Version : If cells in a column contain a value, input text into another column.



Lbaile22
07-19-2018, 12:00 PM
I have a lengthy report with values in column B. They are shape names with other appended info (234Triangle3, 563Square8, 195Circle6). If column B contains text Triangle, Circle, or Square, I want to input "Triangle", "Circle", "Square" into column E. If the text in column B doesn't contain 1 of the 3 conditions, I want to input "N/A" into column E instead. Any help is greatly appreciated.

offthelip
07-20-2018, 10:25 AM
This should do it:

Sub test()
lastrow = Cells(Rows.Count, "B").End(xlUp).Row
inarr = Range(Cells(1, 2), Cells(lastrow, 2))
Range(Cells(2, 5), Cells(lastrow, 5)) = "N/A"
outarr = Range(Cells(1, 5), Cells(lastrow, 5))
For i = 2 To lastrow
If InStr(inarr(i, 1), "Triangle") > 0 Then
outarr(i, 1) = "Triangle"
End If
If InStr(inarr(i, 1), "Square") > 0 Then
outarr(i, 1) = "Square"
End If
If InStr(inarr(i, 1), "Circle") > 0 Then
outarr(i, 1) = "Circle"
End If
Next i


Range(Cells(1, 5), Cells(lastrow, 5)) = outarr


End Sub

Lbaile22
07-20-2018, 11:23 AM
This works perfectly. Thanks!