find match then change cell value

In column A I have cells filled with text and in column B I have cells filled 
with numbers.  I need to check if the number in cell C1 equals any of the 
numbers in column B.  If a match is found then I need to change the text in 
column A to CBO.
e.g.
Column A     Column B    Column C
aep                 5                 7
apa                 0
gci                 59
xto              5000
xle                  7
oih               253
ed                  8

Since the cell C1 = 7 equals the 7 from column B, I need to change the data 
in column A from xle to cbo.  Is this possible?

0
Utf
12/11/2009 7:51:01 PM
excel.programming 6508 articles. 2 followers. Follow

3 Replies
812 Views

Similar Articles

[PageSpeed] 24

Hi,

Try this

Sub aileen()
lastrow = Cells(Cells.Rows.Count, "B").End(xlUp).Row
Set MyRange = Range("B1:B" & lastrow)
For Each c In MyRange
If c.Value = Range("C1").Value Then
    c.Offset(, -1).Value = "cbo"
End If
Next
End Sub

"aileen" wrote:

> In column A I have cells filled with text and in column B I have cells filled 
> with numbers.  I need to check if the number in cell C1 equals any of the 
> numbers in column B.  If a match is found then I need to change the text in 
> column A to CBO.
> e.g.
> Column A     Column B    Column C
> aep                 5                 7
> apa                 0
> gci                 59
> xto              5000
> xle                  7
> oih               253
> ed                  8
> 
> Since the cell C1 = 7 equals the 7 from column B, I need to change the data 
> in column A from xle to cbo.  Is this possible?
> 
0
Utf
12/11/2009 8:20:34 PM
Sub matchAndChange()
Const columnA As String = "A"
Const columnB As String = "B"
Const columnC As String = "C"
Dim iLastRowColumnB As Long
Dim iLooperB As Long
Dim iLastRowColumnC As Long
Dim iLooperC As Long

    iLastRowColumnB = Range(columnB & Rows.Count).End(xlUp).Row
    iLastRowColumnC = Range(columnC & Rows.Count).End(xlUp).Row
    
    For iLooperC = 1 To iLastRowColumnC
        For iLooperB = 1 To iLastRowColumnB
            If Range(columnB & iLooperB).Value = Range(columnC & 
iLooperC).Value Then
                Range(columnA & iLooperB).Value = "CBO"
            End If
        Next
    Next
End Sub

"aileen" wrote:

> In column A I have cells filled with text and in column B I have cells filled 
> with numbers.  I need to check if the number in cell C1 equals any of the 
> numbers in column B.  If a match is found then I need to change the text in 
> column A to CBO.
> e.g.
> Column A     Column B    Column C
> aep                 5                 7
> apa                 0
> gci                 59
> xto              5000
> xle                  7
> oih               253
> ed                  8
> 
> Since the cell C1 = 7 equals the 7 from column B, I need to change the data 
> in column A from xle to cbo.  Is this possible?
> 
0
Utf
12/11/2009 8:28:01 PM
Works perfectly. Thanks so much.

"Mike H" wrote:

> Hi,
> 
> Try this
> 
> Sub aileen()
> lastrow = Cells(Cells.Rows.Count, "B").End(xlUp).Row
> Set MyRange = Range("B1:B" & lastrow)
> For Each c In MyRange
> If c.Value = Range("C1").Value Then
>     c.Offset(, -1).Value = "cbo"
> End If
> Next
> End Sub
> 
> "aileen" wrote:
> 
> > In column A I have cells filled with text and in column B I have cells filled 
> > with numbers.  I need to check if the number in cell C1 equals any of the 
> > numbers in column B.  If a match is found then I need to change the text in 
> > column A to CBO.
> > e.g.
> > Column A     Column B    Column C
> > aep                 5                 7
> > apa                 0
> > gci                 59
> > xto              5000
> > xle                  7
> > oih               253
> > ed                  8
> > 
> > Since the cell C1 = 7 equals the 7 from column B, I need to change the data 
> > in column A from xle to cbo.  Is this possible?
> > 
0
Utf
12/11/2009 8:35:02 PM
Reply:

Similar Artilces: