PDA

View Full Version : Solved: HELP!!! Cannot get code to select cells based on specific text within cell.



EZERYDR
05-18-2011, 09:41 PM
I have been trying to get the code to work that will select cells from a column that contain specified text. I have no problems when using the autofilter"Contains = BR9" but I need the VBA code in a series of "If Range...Then If" statements to make this selection. I have tried the ones listed below and they either choose all cells that match the first 3 if thens or they choose none of them returning blanks.

Then If Range("AT" & Can) = "=*BR9" Then Range("S" & Can) = "PN9"

Then If Range("AT" & Can) = ">=*BR9" Then Range("S" & Can) = "PN9"

Then If Range("AT" & Can) >= "=*BR9" Then Range("S" & Can) = "PN9"

Then If Range("AT" & Can) = "=*BR9*" Then Range("S" & Can) = "PN9"

Then If Range("AT" & Can) = ">=*BR9*" Then Range("S" & Can) = "PN9"

Then If Range("AT" & Can) >= "=*BR9*" Then Range("S" & Can) = "PN9"

The cells in my range (column AT) contain various text such as "BR9", "BL7.BL8.BR9", and "BL7.BL8". I need to return the value "PN9" in cells in column S that "contain" "BR9"....so far I either get none with PN9 or all have PN9.

HELP!!! Please let me know if I need to provide more of the code.....everything else is working fine.

THANKS!!!

Charlize
05-19-2011, 12:13 AM
'I assume can contains a number
If InStr(1, Range("AT" & Can).Value, "BR9") > 0 Then Range("S" & Can).Value = "PN9"
Charlize

ps. one thing I would say is to always define on which workbook and worksheet you are working.

EZERYDR
05-19-2011, 04:42 PM
Thanks Charlize!!! That worked....I appreciate the quick reply.

Keith