macro to put value in cell if word or letter appears in another cell

[oliviar]

Hi guys,
I have to loop through a range in A, and if the letter "C" or the number "9" appears in the cell anywhere (it won't be a whole cell value) then I need column B to show "C".

I know how to do a whole value loop, but I'm stumped on a 'find X anywhere' search.

Thanks

 

[machopicho]

Try this

Sub test()
Dim rng As Range
For Each rng In Range("A1:A10")
If rng.Value = "C" Or rng.Value = "9" Then
rng.Offset(0, 1) = "C"
End If
Next
End Sub

 

[Peter_SSs]

Looping is a relatively slow process, so I try to avoid it where possible.

Try this code in a copy of your workbook.
Note that it looks for an upper case "C" only. If you want to look for "C" or "c" then change the "FIND" in the code to "SEARCH".

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> CheckValues()<br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">With</SPAN> Range("A2", Range("A" & Rows.Count).End(xlUp)).Offset(, 1)<br>        .Formula = "=IF(MIN(FIND({""C"",9},A2&""C9""))<=LEN(A2),""C"","""")"<br>        .Value = .Value<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>


machopicho
I also think you missed this in the OP's post:

the letter "C" or the number "9" appears in the cell anywhere (it won't be a whole cell value)

 

[oliviar]

Thank Peter,
Finally got a chance to use it. Its fast and accurate. Perfect

 

[mirabeau]

Looping is a relatively slow process, ...

I tend to feel somewhat uneasy when I see this kind of unqualified assertion presented as fact.

Peter, of course you use whatever method you like, but would you feel aggrieved if it were shown your assertion is incorrect, in this case at least?

Assuming "relatively" is taken as relative to the method you chose to resolve this particular problem. It seems to be rather that method which is relatively slow.

 

[Peter_SSs]

I tend to feel somewhat uneasy when I see this kind of unqualified assertion presented as fact.

Assuming "relatively" is taken as relative to the method you chose to resolve this particular problem. It seems to be rather that method which is relatively slow.

No, I really meant relative to looping through worksheet cells as had been suggested by the first responder - though you are right, I didn't qualify my remark that way.

... would you feel aggrieved if it were shown your assertion is incorrect, in this case at least?

I'm guessing that in this case you would read the range into an array, loop through the array and output the results back to the sheet. I agree that such a method would be faster than my formula suggestion.

I would not be aggrieved at all to see your code for this, or another method if you had something else in mind - I'm always willing to learn, and don't claim that any of my suggestions are the best possible.

 

[mirabeau]

Peter,

Let me say that I think your code contribution to this thread was good. It was fast, accurate, and made the OP very happy, so what more should a response do?

But your comment about looping being a relatively slow process I think is one a moderator on this forum, particularly of your repute and quality of contribution, really shouldn't be making. Rather put looping (and other techniques too, but you weren't commenting on those) in proper context as tool/technique which may or may not be the best/fastest one for whatever problem is being addressed.

But I made my own comment about your code being slower than need be. Its scarcely fair or reasonable for to make such comment without being prepared to back it up, so I'll do this.

A test data code giving data which seems reasonably in line with OP's problem is

Sub testdata()
[a:c].ClearContents
Dim n, a(), i, j
n = 65000 'or make this 1048576 or whatever you like
ReDim a(1 To n, 1 To 1)
For i = 1 To n: For j = 1 To Rnd * 12 + 5
    a(i, 1) = a(i, 1) & Chr(Int(Rnd * 20) + 97)
Next j
If Rnd < 0.3 Then a(i, 1) = Replace(a(i, 1), Chr(97), 9)
If Rnd < 0.3 Then a(i, 1) = Replace(a(i, 1), Chr(98), "C")
If Rnd < 0.8 Then a(i, 1) = Replace(a(i, 1), Chr(99), " ")
Next i
Cells(1).Resize(n) = a
End Sub

And a timed, looping code, which gives the same results for these test data as does yours

Sub loopingcode()
Dim t As Single
t = Timer
Dim n&, a, i&, j&, u()
n = Range("A" & Rows.Count).End(3).Row
ReDim u(1 To n, 1 To 1)
a = Range("A1").Resize(n)
'and loop comes next
For i = 2 To n
    If (InStr(a(i, 1), "C") > 0) _
        + (InStr(a(i, 1), 9) > 0) Then u(i, 1) = "C"
Next i
Cells(3).Resize(n) = u
MsgBox "Code took " & Format(Timer - t, "0.000 secs")
End Sub

I don't generally like to comment on other contributors' codes (except as seems necessary vide Post#5 above) so please do whatever you like about yours.

 

[MChern]

Peter,

maybe you could indicate, how to alter the code, so that it leaves the cells not satisfying the criteria and a next IF (for example to show a "B" if "B" in column A is met) can be run without erasing the results of the first IF?

Kind regards,

 

[Peter_SSs]

Peter,

maybe you could indicate, how to alter the code, so that it leaves the cells not satisfying the criteria and a next IF (for example to show a "B" if "B" in column A is met) can be run without erasing the results of the first IF?

Kind regards,

If this is not what you mean, please provide some sample data and the corresponding expected results, say 8-10 rows of varied data.
<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> CheckValues()<br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">With</SPAN> Range("A2", Range("A" & Rows.Count).End(xlUp)).Offset(, 1)<br>        .Formula = "=IF(MIN(FIND({""C"",9},A2&""C9""))<=LEN(A2),""C"",IF(MIN(FIND(""B"",A2&""B""))<=LEN(A2),""B"",A2))"<br>        .Value = .Value<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br><br></FONT>

 

[jkase75]

Hey I was wondering for this script how would I look up an array of words and if those specific words are with in that column assign them a specific word to the adjacent cell?

Sub CheckValues()
Application.ScreenUpdating = False
With Range("A2", Range("A" & Rows.Count).End(xlUp)).Offset(, 1)
.Formula = "=IF(MIN(FIND({""C"",9},A2&""C9""))<=LEN(A2),""C"","""")"
.Value = .Value
End With
Application.ScreenUpdating = True
End Sub