Question

Find and replace in other colums.Excel Macro that can do this.

Asked by: bsharath

Hi,

Find and replace in other colums.Excel Macro that can do this.
Say in colum "V" i have
8123
8789
7890
7871
8988
I want a script to check colum "V" if found such data then replace colum "U" with the word "LENOVA"

Any help would be useful.

Regards
Sharath

This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.

Subscribe now for full access to Experts Exchange and get

Instant Access to this Solution

  • Plus...
  • 30 Day FREE access, no risk, no obligation
  • Collaborate with the world's top tech experts
  • Unlimited access to our exclusive solution database
  • Never be left without tech help again

Subscribe Now

Asked On
2009-03-31 at 07:30:21ID24281176
Tags

Excel

,

Macro

Topics

Microsoft Excel Spreadsheet Software

,

Microsoft Applications

,

Microsoft Office Suite

Participating Experts
2
Points
500
Comments
12

Trusted by hundreds of thousands everyday for fast, accurate and reliable tech support.

  • "The time we save is the biggest benefit of Experts Exchange to Warner Bros. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange." Mike Kapnisakis, Warner Bros.
  • "Our team likes having a resource that is more secure than just using Google and most experts using this service really know their stuff. It's nice to look here first versus using Google." Dayna Sellner, Lockheed Martin
  • "Anytime that I've been stumped with a problem, 9 out of 10 times Experts Exchange has either the accepted solution or an open discussion of the potential solution to the problem." Kenny Red, eBay Inc.

See what Experts Exchange can do for you.

Got a question?

We've got the answer.

Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.

Screenshot of Experts Exchange Knowledgebase

Need individual assistance?

Our experts are ready to help.

If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.

Screenshot of Experts Exchange Knowledgebase

Want to learn from the best?

Read articles from industry experts.

Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.

Screenshot of an Article

Working on a long term project?

Store your work and research.

Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.

Screenshot of Experts Exchange Knowledgebase

Access the answers to your technology questions today.

Subscribe Now

30-day free trial. Register in 60 seconds.

What Makes Experts Exchange Unique?

Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.

Trusted by the world's most respected brands.

image of each brand's logo

Faithfully serving IT professionals since 1996.

Experts Exchange Logo

Try it out and discover for yourself.

Subscribe Now

30-day free trial. Register in 60 seconds.

Related Solutions

  1. Excel Macro to put data in colum R checking the Colum Q bol…
    Hi, Excel Macro to put data in colum R checking the Colum Q bold data. I have a excel in which Colum Q data has many in bold.Is there a way that a macro checks the Q colum where ever found bold data in the adjacent colum R put the date as this " 03-02-2008" Regar...
  2. Find colum A data and replace with colum B data in sheet 1…
    Hi, Find colum A data and replace with colum B data in sheet 1 from sheet2 What i want is there are changes that i need to do with names. Ex: Sheet2 has Colum A Colum B Sharath changed to SharathC ...
  3. Excel macro to find all groups a user is a member and place…
    Hi, Excel macro to find all groups a user is a member and place the data in the comments in Colum E. I have the NTlogins in Colum "M" a macro gets the groups a user is a member and puts the data in a comment and places it in Colum "E" This would be so use...
  4. Can we run Macro's 1 by one as mentioned in colum A.
    Hi, Can we run Macro's 1 by one as mentioned in colum A. I have a lot of macro's which take lot of time quering. Is there a way i run a macro which can query sheet 1 in colum 1 for names of the macros and run them 1 after the other. So that i can run them in the nights and ...
  5. Find data in colum A if match found in colum "T" then repl…
    hi, Find data in colum A if match found in colum "T" then replace it with the data in colum B. I have a sheet called 'Desktops" that has the colum "T" data. need to compare the colum A data in another sheet and if found match replace the "B"...
  6. Excel macro need to clear a colum before doing the other ta…
    Hi, Excel macro need to clear a colum before doing the other task. The macro attached in the excel copied data from B to BO. What i want is just clear the whole colum of "BO" before doing this task of inserting data. Regards Sharath

Free Tech Articles

  1. WARNING: 5 Reasons why you should NEVER fix a computer for free.
    It is in our nature to love the puzzle. We are obsessed. The lot of us. We love puzzles. We love the challenge. We thrive on finding the answer. We hate disarray. It bothers us deep in our soul. W...
  2. SCCM OSD Basic troubleshooting
    SCCM 2007 OSD is a fantastic way to deploy operating systems, however, like most things SCCM issues can sometimes be difficult to resolve due to the sheer volume of logs to sift through and the dispe...
  3. Migrate Small Business Server 2003 to Exchange 2010 and Windows 2008 R2
    This guide is intended to provide step by step instructions on how to migrate from Small Business Server 2003 to Windows 2008 R2 with Exchange 2010. For this migration to work you will need the fo...
  4. Create a Win7 Gadget
    This article shows you how to create a simple "Gadget" -- a sort of mini-application supported by Windows 7 and Vista. Gadgets can be dropped anywhere on the desktop to provide instant information, ...
  5. Outlook continually prompting for username and password
    There have been a lot of questions recently regarding Outlook prompting for a username and password whilst using Exchange 2007. There are a few reasons why this would happen and I will try to cover t...
  6. Backup Exchange 2010 Information Store using Windows Backup
    There seems to be quite a lot of confusion around the ability to backup Exchange 2010 using the built in Windows Backup feature. This stems from the omission of this feature prior to Exchange 2007 s...

Cloud Class Webinars

  1. Avoiding Bugs in Microsoft Access
    Alison Balter takes and in-depth look at avoiding bugs in Access. In this webinar you will learn about using the immediate window to debug your applications, invoking the debugger, using breakpoints to troubleshoot, stepping through code, setting the next statement to execute, ...
  2. Top 10 Best New Features in Visio 2010
    Scott Helmers gives live demonstrations of the top 10 new features in Visio 2010. This webinar will teach you how to create compelling diagrams by adding shapes to the page with a single click, linking the shapes in a diagram to data in Excel (or SQL Server, or SharePoint), ...
  3. IT Consultant Business Secrets Revealed
    Michael Munger, Experts Exchange tech pro and IT consultant, pulls back the curtain on his very successful businesses and answers question on every IT consultant and business owner should know about. He shares secrets on what he did to solve the 5 most common problems in IT, ...
  4. Disaster Recovery and Business Continuity
    Quest CTO, Mike Billon, gives an overview of the steps involved in building a dunamic disaster recovery plan. Through case studies and an examination of software/hardware tooles for monitoring and testing, you'll gain a better understandin of where you are, where you want ...
  5. Organize Your Visio Diagrams with Containers and Lists
    Scott Helmers uses cross functional flowcharts, wireframe diagrams, data graphic legends and seating charts to teach you: how to ustilize all three new structured diagram components in Visio 2010, the best practices for organizeing shapes in previous version of Visio, how to organize ...
  6. How to Us Objects, Properties, Events and Methods in Microsoft Access
    Alison Dalter gives an in-depbth look at objects, properties, events and methods in Microsoft Access. In this webinar you will learn about using the object browser, referring to objects, working with properties and methods, working with object variables, understanding the ...

Join the Community

Give a Little. Get a Lot.

Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.

Join the Community

Answers

 

by: pari123Posted on 2009-03-31 at 08:45:00ID: 24030161

Sharath,

How are u? Can you post a sample file please?

- Ardhendu

 

by: bsharathPosted on 2009-03-31 at 08:55:21ID: 24030296

Hi pari i am doing fine... howz u

Attached is the sample file

 

by: pari123Posted on 2009-03-31 at 11:10:37ID: 24031799

Sharath,

>> "its has to check all the data in the code that I mention aand replace just the Colum U data to "LENOVA"

Can you explain what data are you refering to here?  what is the data you want to replace with LENOVA and why can't you do a simple find-replace?

- Ardhendu

 

by: bsharathPosted on 2009-03-31 at 11:18:42ID: 24031892

I need to compare the models in colum "V" and if found with my 5 or more types of data that i mention in the code then replace what ever is there in the adjacent with 'LENOVA" thats in Colum "U"

Colum "U" has make details "brand" details.

Say in the example colum "V" i have
8123
1787
8976
I want to mention these no's in the code so. Find where ever 8123 or 1787 or 8976 is there in colum "V" if found place or replace "Lenova" in the adjacent colum "U" with it.

 

by: bsharathPosted on 2009-03-31 at 11:18:44ID: 24031893

I need to compare the models in colum "V" and if found with my 5 or more types of data that i mention in the code then replace what ever is there in the adjacent with 'LENOVA" thats in Colum "U"

Colum "U" has make details "brand" details.

Say in the example colum "V" i have
8123
1787
8976
I want to mention these no's in the code so. Find where ever 8123 or 1787 or 8976 is there in colum "V" if found place or replace "Lenova" in the adjacent colum "U" with it.

 

by: bsharathPosted on 2009-03-31 at 11:22:51ID: 24031950

 

by: netcepterPosted on 2009-03-31 at 12:06:37ID: 24032402

Not a vb script but a quick fix in column T as a formula.  Just copy and past value. That is if you don't have a huge amount to change.  


net

 

by: pari123Posted on 2009-03-31 at 14:38:59ID: 24033963

Hi Sharath,
Try the following code.

1. First save a txt file with the text that you want to search.
2. Then execute the macro and see if this works,

Ardhendu

Option Explicit
Sub SearchMacro()
 
Dim txtSrch As String, _
flName As String, _
TryAgain As String, _
i As Integer, _
ws1 As Worksheet, _
ws2 As Worksheet, _
wb1 As Workbook, _
wb2 As Workbook, _
rngX As Range
 
Application.ScreenUpdating = False
Application.DisplayAlerts = False
 
Set ws1 = ActiveSheet
MsgBox ("Please select the txt file.")
StartHere:
flName = Application.GetOpenFilename
 
 
If flName = "False" Or Right(flName, 3) <> "txt" Then
    TryAgain = MsgBox("You did not select a valid file.  Do you want to try again?", vbYesNo + vbCritical, "Invalid Entry")
    If TryAgain = vbNo Then
       MsgBox "No file was selected. Exiting From Macro.", vbCritical + vbOKOnly
       Exit Sub
    Else
        GoTo StartHere
    End If
Else
    Workbooks.Open Filename:=flName
    Set wb2 = ActiveWorkbook
    Set ws2 = ActiveSheet
    For i = 1 To ws2.Cells(65536, "A").End(xlUp).Row
        Set rngX = ws1.Range("V:V").Find(ws2.Range("A" & i), lookat:=xlWhole)
        If Not rngX Is Nothing Then
            rngX.Offset(0, -1) = "LENOVA"
        End If
    Next
End If
wb2.Close
 
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub
                                              
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:

Select allOpen in new window

 

by: bsharathPosted on 2009-03-31 at 20:21:02ID: 24035740

Thank U Pari worked Awesome....
netcepter thanks for the help but a macro is what i wanted in this case... :-)

 

by: pari123Posted on 2009-03-31 at 20:21:48ID: 24035742

sharath,
you are always welcome....

- Ardhendu

 

by: bsharathPosted on 2009-03-31 at 20:32:20ID: 24035779

 

by: bsharathPosted on 2009-04-15 at 10:06:19ID: 24150285

20120131-EE-VQP-002

3 Ways to Join

30-Day Free Trial

The Experts

98% positive feedback on 31,087 answers since March 2000. angeliii is a Microsoft Most Valuable Professional for his work with MS SQL Server & Develoment.

He has also proven his knowledge of Visual Basic Programming, PHP Scripting and Oracle Databases.

The Experts

97% positive feedback on 10,752 answers since July 2000. lrmoore has more than 18 years experience in the networking industry.

The six-time Mircosoft MVPs specialties include firewalls, virtual private networking, and network management.

Testimonials

"...and excellent source for support... Kind of like having your very own IT dept." Electriciansnet

Testimonials

"I was apprehensive at signing up at first. However... it has already made my life as an IT administrator much easier." JaCrews

Testimonials

"WOW! You guys have great, active, and knowledgeable people on here." moore50

Business Clients

Business Clients

In the Press

"If you’ve got a question... Experts Exchange can supply an answer.”

In the Press

"...an invaluable aid for both IT professionals and those who require tech support."

In the Press

"where IT professionals provide quick answers on just about any topic"

Business Account Plans

Loading Advertisement...