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
763 Views

Similar Articles

[PageSpeed] 57

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:

How to send message to advanced find results?
Running Outlook 2003. I run an advanced query to get a list of contacts. Now I have exactly the list of contacts I want to email in the Advanced Find results dialog. Is there an easy way to send an e-mail to everyone in the results list? Thanks for your help. Select all, drag to your inbox? --=81 Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. All unsolicited mail sent to my personal account will be deleted without reading. =20 After furious head scratching, Tom C. asked: | Running Outlook 2003. I run an advanced query to get a list of | c...

DL size limit change
We have a user thats trying to save a distribution list to the server. He is trying to save 95 names. We are using excg5.5, Is their a setting on the server that limits the amount of names that can be saved in a dl. If so can it be changed, and where can it be changed. Also does outlook have dl size limits, how can these be changed? Thanks in advance there is a limit on personal distribution lists - I think it is 165 members - but I have DLs on Exchange servers that contain over 2000 names. Anything that needs more members than that, I nest...how is your user trying to do this? only Ex...

How to put Time or Date Only in Cell 1) Time HHMMSS 2) Date YYYYMMDD
I need to have a cell = the current time in the format HHMMSS And when I export as Comma Delimited or Fixed Length Ascii, I need for it to remain in that format. Ditto for Date as YYYYMMDD I've played around with both =TIME and =DATE but no luck. Can anyone tell me how to do this? AND, once the forumula is in the cell... how can I get it to update to the current time or date? thanks for any help I formatted my times as hhmmss and my dates as yyyymmdd and then did a file|saveAs. I chose the comma separated values (*.csv) and I got what you wanted. I opened it in notepad to ver...

Concatenate text & number cell
Hi all, I need to concatenate a word and a number cell. eg, Concatenate("Limit = $",A1) where A1 = $100,000. It shows up like this: "Limit = $100000". However I want the number to format with the comma between the thousand, ie. "Limit = $100,000" Does any one know how to do this. Thanks in advance. regards, John. one way: ="Limit = " & TEXT(A1,"$#,##0") In article <2285c01c45d99$ff299070$a301280a@phx.gbl>, "John" <anonymous@discussions.microsoft.com> wrote: > Hi all, > I need to concatenate a wo...

Field changed from Single to Double issue..
I have an Access 2000 DB (I think it's 2000, it's whatever comes with Visual Studio 6.0).. Simply put, I have a field that is of type "Single" that contains the value 0.3. When I modify that field and make it "Double", access automatically converts the values, as it should, but returns 0.300001192 in the field.. I understand that there can be precision issues sometimes in comparisons and such, but is this NORMAL for Access to do as well? Is there any simple work around or patch? I suppose I can write an SQL statement to take care of this, but that isn...

Formating Bars based on the value of a bar
I have created a bar chart in an access report. I want the bars to change color based on the value of the specific bar. For example, if the value of a bar is less than 1, it is red, if it is between 1 and 5, it is yellow, if it is greater than 5 it is green. How can I do this? Seems like an easy problem, but I've just spent a couple of hours playing with this and I can't make it work. The problem is that the Value property of the points in the SeriesCollection is not exposed in the VBA interface. It certainly exists (eg. with the chart in design mode, if you hover the mouse...

Changing Vendor ID on 1200 Inventory Cards
Is there an easy way to update one Vendor on 1200 Inventory cards with another vendor. I don't want to delete the first vendor I just want to add a vendor ID to these cards. All the item numbers have an identifing number. ie. all start with MEL Thanks, Gloria Do you want to replace the vendor on all the items with a new one? Write a sql script like: update IV00103 set VENDORID='yournewvendor' where ITEMNMBR like 'MEL%' If there are no vendors assigned to the items and you want to add records, then you will need to do an insert statement into the IV00103 with t...

Changing space before and after paragraphs does nothing
I am using PP 2003. I am trying to format text in a text box within an existing presentation. I set the line spacing for the whole text box, and that works fine. However, changing the line spacing before and after parapraphs does absolutely nothing. I have checked to make sure that I have put hard returns in to separate paragraphs, not soft returns. This problem does not happen if I create a new presentation from scratch. However, I cannot afford to start from scratch, as I am editing a very long, existing presentation, and it would take me forever to start all over again. Something i...

Finding Info in Userforms
Hi, I am a total noob when it comes to userforms. So far I have learnt the basics like inserting a userforum, renaming text and command boxes, assigning some vb language to the cmmd buttons and running the userform. Now I would like to learn how to implent the "find" command into my userform? Also I would learn how to implement the "edit" feature to my userform so that it changes a record and saves it? I have no knowledge of VB and I am totally new to this. Please help Thanks in advance..... -- xirokx ---------------------------------------------------------------...

Cells formated as text do not always display properly
I am using Office XP and Windows XP. I have formatted a column of cells as text and word wrap is checked, horizontal alignment is Left, Vertical alignment is Bottom. I use these cells to type a short narrative. The problem is some of the cells will not display properly, all they show is ######. The row height will expand to hold the text sometimes but not always but it will still only show ######## instead of the text I typed. If I change the cell type to General all the text is visible. This does not happed to evey cell, only some of them. I can type several rows of data with a narr...

How to find Display Names that differ from form field labels
Given the fact that CRM can be customized to use a field label on the form that is different from the Display Name of the attribute, I would suggest that users be given some kind of tool to identify the Display Name and/or Schema Name when they are on the form. There are a few good reasons to have the field label differ from the display name, but users may be confused when they can't find that on the list of fields in the Advanced Find. In addition, some power users (who don't have admin rights) may create SRS reports, and therefore need the Schema Name. ---------------- This...

Referenced cell displays formula, not data
I downloaded a form template and linked it to my data; when I use the = sign to insert my data into the template, some – though not all – cells display the formula of the cell reference rather than the data that is actually contained in the cell Example: Cell D10 in datasheet contains text; instead of displaying the text in the template, it shows the following formula that is the reference address: ='[data sheet.xlsx]'!$D$10 -- MZ Press CNTRL + TILDE Key whch is available above the Left Tab Key. Tilde key (`) or (~) Remember to Click Yes, if this post helps! ...

Compare dates (one cell not in date format)
Hi all, I have one column of cells in date format (6/14/2005) and anothe column of cells which also contains a date pulled from a database bu this second column is not in date format, it comes from the databas like this 2005Jan20. I need to compare the two dates to work out th number of days between the dates but I can't convert the second date t date format. Is there any way I can work out the number of days between the tw dates? Thanks in advance Crai -- craigcs ----------------------------------------------------------------------- craigcsb's Profile: http://www.excelforum.com/...

Index and match
I have a spreadsheet with 2 pivot tables side by side. One pivot table is created using data from cash application and the other pivot table is from bank transactions. The two pivot tables are compared for missing data either in cash application or in bank. The data is the dollar amount obviously. I had used the formula =IF(COUNTIF(F8,"*Total")," ",INDEX(GLCC,MATCH(H8,GLCC,0),0)). GLCC is a name for a defined range and I wanted a blank if matching title has "Total". The formula mostly works but in lots of cases #N/A is displayed even though there is...

Summing values associated with mixed labels
I have a long spreadsheet which records the dates, descriptions, sources, and quantities of various purchased items. Simplified, a description (gloves) appears in Column B and the quantity purchased appears in Column D. Lets say that one day I bought 3 gloves, two days later I bought 6, and three days after that I bought 2. Mixed in with that I bought a number of other things, and sorting is not an option. How can I look through the system and add up all the values in Column D anytime "gloves" appears in Column B? *** Sent via http://www.automationtools.com *** Add a newsgroup inte...

I cannot find the key to activate my Office trial!!
A 60 days trial version was installed on my Asus computer. I didn't have to download anything, and therefore I haven't registered my mail address either. How can I get the trial key? Is there an Activation Assistant shortcut on your desktop? PS registration is optional, nothing to do with activation "Mads" <Mads@discussions.microsoft.com> wrote in message news:BA04C732-AF3A-4AC5-B817-45405E9A37E3@microsoft.com... >A 60 days trial version was installed on my Asus computer. I didn't have to > download anything, and therefore I haven't registere...

OE6 email font
When I create an email, I like to use the Bookman font. OE will not let me change it to this and the font size only changes slightly when I choose a new font size. This newsgroup is for support of Outlook 97, 98, 2000 & 2002 from the Office family for Windows PCs. For Outlook Express (OE) support try posting in one of these newsgroups: microsoft.public.inetexplorer.ie4.outlookexpress for OE 4.x microsoft.public.windows.inetexplorer.ie5.outlookexpress for OE 5.x http://support.microsoft.com/newsgroups/default.aspx?ICP=GSS3&NewsGroup=microsoft.public.windows.inetexplorer.ie5.outlook...

date problem--excel changing entry
I want date to show as 11/05/03 and not have to enter the / or a -. Have formatted cells to date and that but excel ix changing to a totally different date----7/19/06? You could use a macro that converts numeric entries to dates. Chip Pearson has shared his: http://www.cpearson.com/excel/DateTimeEntry.htm Or if you're consistent (leading 0's for days and months), you could format the column(s) as text, do your data entry and then use data|text to columns to convert them to real dates (choose mdy as the field type). D Blount wrote: > > I want date to show as 11/05/03 and...

How do I anchor adjacent cells ?
Col A of spreadsheet (the recipient) contains Anchored cell refs to another workbook (the source), and then I enter info in Col B which relates only to the cell in Col A (eg B1 to A1 etc). If I later insert rows within the data of the source workbook, and update the recipient spreadsheet upon opening it, the data in Col B no longer matches the cell in Col A. Is there any way of keeping the relationship between the adjacent cells, ie some form of anchoring, so that this does not happen? Two thoughts: 1) make sure both spreadsheets are open when you change the structure of the source wo...

Need to add a line with a value in addition to x & y axis
I am trying to create a chart in Excel as clustered columns with th following data (I have also attached a copy of my file from Exce 2002): Division 1 Division 2 Division 3 Division 4 Division 5 2002 18.60 3.49 5.14 8.52 3.75 2003 14.54 3.53 1.96 10.22 7.29 2004 3.50 1.77 0.00 7.28 3.71 The years are shown in the legend, the category axis (division names are at the bottom, and the value axis is on the side. There is also a industry standard that applies to all values (not by year) and I want t have a line that shows where this value is in relation to the yearl values for each division. I ...

changing field size
Hello there if i have form with data that i present as datasheet is there a way to diterme the field size by code? hi Roy, Roy Goldhammer wrote: > if i have form with data that i present as datasheet is there a way to > diterme the field size by code? Take a look at the object explorer in the VBA IDE: go to the VBA IDE and press F2. Search for TextBox and examine the column properties. mfG --> stefan <-- Thankes Stefen i found out the Column with property for that. Now is there a way so set the size of texbox or checkbox in datasheet view automaticly by the largest...

How can I find the alpha numeric codes in excel please,Thank you.
Can someone please tell me how I can find the alpha numeric codes in excel and also are they english,greek or other ? Thank you. choose font <symbols> and type a you will get alpha character map in system tools will have alphanumeric codes in English =char(65) is A =char(90) is Z =char(97) is a etc "Crazylady_2" <Crazylady_2@discussions.microsoft.com> wrote in message news:5E69866F-FB14-47A4-A662-83087A361B22@microsoft.com... > Can someone please tell me how I can find the alpha numeric codes in excel > and also are they english,greek or other ? Thank you. ...

Hide text that doesn't fit in the cell
How can I hide the part of the text that is too long for the cell? This automaticly happens when in the cell on the right is already a text. But I want this also when there is no text in the neighbouring cell. (Note: I don't want to use wrap text) Hi see your other post -- Regards Frank Kabel Frankfurt, Germany "Rene" <Rene@discussions.microsoft.com> schrieb im Newsbeitrag news:51EBA07E-568C-4036-A64E-F6E31A1E6AC4@microsoft.com... > How can I hide the part of the text that is too long for the cell? This > automaticly happens when in the cell on the right is al...

Changing Background Color Only
I have a table in the middle of my sheet. The background is just the normal white cells. I want to change the entire background to a solid color, but I just can't get it to work. Here is what I tried. I highlighted my table, held down the ctrl key, then highlighted the entire page, and then filled in the color...but this doesn't work. Any suggestions? Thanks If you want just the table to be coloured, then highlight the cells making up the table and click on the fill colour icon from the formatting toolbar (if visible - if not then Format | Cells | Patterns) and then click the co...

If cell contatin string like .... copy in Offset mode
Hi , and a good day everybody Please to help me with this problem : I have in a range (f or example : A1:E100 ), in different cells , numbers like in the follow format /(string) like : 12-16-18-25-33-43 ; The other cell contain another values , like : 3 , - , etc . I need a code to loop through my range cell by cell , and , IF the value of cell is in string mode like : 17-28-34-36-39-42 ( can be different numbers , but with the sign - beetwen them ) to copy them in another range , for example G1 , useing Offset function , like : Offset ( 1, 0) . A lot of tha...