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

oliviar

Board Regular
Joined
Sep 12, 2010
Messages
184
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 :cool:
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

machopicho

Well-known Member
Joined
Feb 28, 2010
Messages
606
Try this
Code:
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
50,694
Office Version
  1. 365
Platform
  1. Windows
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)
 
Last edited:

oliviar

Board Regular
Joined
Sep 12, 2010
Messages
184
Thank Peter,
Finally got a chance to use it. Its fast and accurate. Perfect
 

mirabeau

Banned user
Joined
Nov 4, 2010
Messages
2,075

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
50,694
Office Version
  1. 365
Platform
  1. Windows
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

Banned user
Joined
Nov 4, 2010
Messages
2,075

ADVERTISEMENT

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
Rich (BB code):
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
Rich (BB code):
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.
 
Last edited:

MChern

New Member
Joined
Jul 3, 2012
Messages
2
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
50,694
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Feb 19, 2015
Messages
1
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
 

Forum statistics

Threads
1,141,847
Messages
5,708,946
Members
421,601
Latest member
Garlo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top