Searching thru a column for text and changing another cell with text

[zqcebtmu]

Hey guys,

I'm new to the forum and I'm running in to a small issue where I have to go thru a thousand odd cells of data in a column and change another cell in the same row with text. For example:

2 A
3 B
4 C
10 D
6 E
4 F
6 G
10 H
10 I
7 J
7 K
10 L
8 M
6 N
4 O
9 P
3 Q
2 R
9 S
4 T
6 U
2 V
2 W
9 X
7 Y
6 Z

I have to go thru the first column and search for "10" and then replace it's neighboring cell with the word "TEN". Is there a way to automate this process?

 

[sericom]

Something like this should do it

Sub FindData()
    Set Rng = Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row)
    For Each C In Rng
        If C.Value = 10 Then C.Offset(0, 1) = "TEN"
    Next
End Sub

It assumes your data is in columns A and B and starts at row 2.

To use it, press Alt+F11 to open up VB editor, then right click your workbook VBAProject and select insert module and paste in the code.
Press the Green Run button or F5 to run the code.
The module will also be visible back in excel if you click the Macros button in the developer tab.

 

[zqcebtmu]

What if my A column contains strings of text and I'm searching for key words? The output is still similar. For example:

COLUMN A COLUMN B
This cell contains 9 A
This cell contains 8 B
This cell contains 10 C
This cell contains 1 D
This cell contains 2 E
This cell contains 9 F
This cell contains 3 G
This cell contains 4 H
This cell contains 4 I
This cell contains 1 J
This cell contains 6 K
This cell contains 3 L
This cell contains 5 M
This cell contains 5 N
This cell contains 1 O
This cell contains 8 P
This cell contains 7 Q
This cell contains 1 R
This cell contains 1 S
This cell contains 7 T
This cell contains 7 U
This cell contains 8 V
This cell contains 4 W
This cell contains 1 X
This cell contains 8 Y
This cell contains 1 Z

 

[Michael M]

I think you need to post a real example of your data to get an appropriate answer
We could provide a response and then find out that the number is within the text, for example !!

 

[sericom]

Change the If line to

If InStr(1, C.Value, "10") Then C.Offset(0, 1) = "TEN"

 

[zqcebtmu]

Thanks sericom! Worked just fine The data is sensitive so I just needed the basic code to tweak to work for my spreadsheet. Thanks so much!