Macro Replace Column A with Column B

Howdy,

I've got a list of 250 some odd abbreviations and full codes for excel
data. I want to run a macro that will search for A1, then replace it
with the value in B1, then A2 with B2, and so forth until the end of
data in column A. Any ideas on how I could do this? Btw, is it possible
to store the conversions in the macro workbook, so it isn't "lost". IE,
could I make a sheet1 in personal.xls, then have the macro check
personal.xls, sheet1, column A, then replace with personal.xls, sheet1,
column B?

Hope this makes sense!
Thanks in advance!

Austin

0
admailtx (4)
7/19/2006 10:15:05 PM
excel 39879 articles. 2 followers. Follow

6 Replies
307 Views

Similar Articles

[PageSpeed] 42

bump

0
admailtx (4)
7/25/2006 6:44:02 PM
I don't quite understand - can't you just highlight column A and delete
it?

Pete

admailtx@gmail.com wrote:
> bump

0
pashurst (2576)
7/26/2006 12:33:54 AM
The list contains abbreviations and the corresponding word it belongs
too. It is a list of countries and Postal abbreviations. I want to use
that list to search for abbreviations in another file/sheet and replace
it with the full word. For example, fie.xls has a list of people with
foreign addresses. Personal.xls, the macro workbook, contains a sheet
named abbrevaitions. I want the macro to search for the abbreviation in
file.xls, and replace it with the data from personal.xls!abbrevaitions.


Pete_UK wrote:
> I don't quite understand - can't you just highlight column A and delete
> it?
> 
> Pete
> 
> admailtx@gmail.com wrote:
> > bump

0
admailtx (4)
7/31/2006 8:49:21 PM
You could use VLOOKUP to do this - check out Excel Help, or post more
details of your actual columns if you want help here.

I'm not sure why you have put the data in Personal.xls - if you use
VLOOKUP to a different file, you will need to include the full path and
filename, and I think Personal.xls can exist in different locations.
Why not set up a folder directly in C:, eg C:\Ref_data and then put the
file in this folder - then you will always know the path to it.

Hope this helps.

Pete

admailtx@gmail.com wrote:
> The list contains abbreviations and the corresponding word it belongs
> too. It is a list of countries and Postal abbreviations. I want to use
> that list to search for abbreviations in another file/sheet and replace
> it with the full word. For example, fie.xls has a list of people with
> foreign addresses. Personal.xls, the macro workbook, contains a sheet
> named abbrevaitions. I want the macro to search for the abbreviation in
> file.xls, and replace it with the data from personal.xls!abbrevaitions.
>
>
> Pete_UK wrote:
> > I don't quite understand - can't you just highlight column A and delete
> > it?
> > 
> > Pete
> > 
> > admailtx@gmail.com wrote:
> > > bump

0
pashurst (2576)
8/1/2006 8:55:05 AM
I'm looking into VLOOKUP now, I've seen it before, but never used it.
Here is a sample of what I need to do. I have a list of countries and
their abbreviations in an excel file. Here's a sample:
Code	Country Name
AF	AFGHANISTAN
AL	ALBANIA
AG	ALGERIA
AQ	AMERICAN SAMOA
AN	ANDORRA
AO	ANGOLA

I have other lists of people, with address info. In this file, the
country field contains abbreviations. Here's a sample of the data:
Name     Address    Country  Postal Code
Bob     123 Main       AF             1235
Joe      456 5th Ave   AN             8764
Susie    789 Hwy 6    AG            5468

I want to replace the abbrevation with the full country name. So the
macro would look through the country list, and replace the abbrevations
with the full country name. So the above data would become:
Name     Address    Country  Postal Code
Bob     123 Main       AFGHANISTAN             1235
Joe      456 5th Ave   ANDORRA             8764
Susie    789 Hwy 6    ALGERIA            5468

Does this make sense?

I want to put the info in Personal.xls because I only run the macro on
this computer. All my macros are in that workbook, so mobility isn't
really an issue.

Thanks for your help, it is greatly appreciated!

Austin

Pete_UK wrote:
> You could use VLOOKUP to do this - check out Excel Help, or post more
> details of your actual columns if you want help here.
>
> I'm not sure why you have put the data in Personal.xls - if you use
> VLOOKUP to a different file, you will need to include the full path and
> filename, and I think Personal.xls can exist in different locations.
> Why not set up a folder directly in C:, eg C:\Ref_data and then put the
> file in this folder - then you will always know the path to it.
>
> Hope this helps.
>
> Pete
>
> admailtx@gmail.com wrote:
> > The list contains abbreviations and the corresponding word it belongs
> > too. It is a list of countries and Postal abbreviations. I want to use
> > that list to search for abbreviations in another file/sheet and replace
> > it with the full word. For example, fie.xls has a list of people with
> > foreign addresses. Personal.xls, the macro workbook, contains a sheet
> > named abbrevaitions. I want the macro to search for the abbreviation in
> > file.xls, and replace it with the data from personal.xls!abbrevaitions.
> >
> >
> > Pete_UK wrote:
> > > I don't quite understand - can't you just highlight column A and delete
> > > it?
> > > 
> > > Pete
> > > 
> > > admailtx@gmail.com wrote:
> > > > bump

0
admailtx (4)
8/1/2006 9:34:49 PM
If you want to add another column to your data, you can use an =vlookup()
formula in that other column to return the country name.

=vlookup(c2,sheet2!a:b,2,false)
Debra Dalgleish has some notes:
http://www.contextures.com/xlFunctions02.html  (for =vlookup())

If you want to try to do it with a series of edit|replaces (via a macro), you
can use this:

Option Explicit
Sub testme()

    Dim wkbk As Workbook
    Dim myRng As Range
    Dim myCell As Range
    Dim wks As Worksheet
        
    With ThisWorkbook.Worksheets("Sheet1")
        Set myRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))
    End With
    
    Set wkbk = ActiveWorkbook
    If wkbk.FullName = ThisWorkbook.FullName Then
        MsgBox "Please activate the workbook to be fixed!"
        Exit Sub
    End If

    For Each myCell In myRng.Cells
        With Selection.EntireColumn
            .Replace what:=myCell.Value, _
                     replacement:=myCell.Offset(0, 1).Value, _
                     lookat:=xlWhole, _
                     searchorder:=xlByRows, _
                     MatchCase:=False
        End With
    Next myCell

End Sub

Select a cell in the correct column (or columns) and then invoke the macro.

I wouldn't put this in my personal.xls file, though.  I'd just create another
workbook with the list and the code.  If I needed it, I'd just open that
workbook, activate my "real" workbook.

Select the correct sheet, select the correct column and tools|macro|macros|run
the macro.

That way, I could share this little program with others and not have to worry
about sharing my personal.xls file.


If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm



admailtx@gmail.com wrote:
> 
> I'm looking into VLOOKUP now, I've seen it before, but never used it.
> Here is a sample of what I need to do. I have a list of countries and
> their abbreviations in an excel file. Here's a sample:
> Code    Country Name
> AF      AFGHANISTAN
> AL      ALBANIA
> AG      ALGERIA
> AQ      AMERICAN SAMOA
> AN      ANDORRA
> AO      ANGOLA
> 
> I have other lists of people, with address info. In this file, the
> country field contains abbreviations. Here's a sample of the data:
> Name     Address    Country  Postal Code
> Bob     123 Main       AF             1235
> Joe      456 5th Ave   AN             8764
> Susie    789 Hwy 6    AG            5468
> 
> I want to replace the abbrevation with the full country name. So the
> macro would look through the country list, and replace the abbrevations
> with the full country name. So the above data would become:
> Name     Address    Country  Postal Code
> Bob     123 Main       AFGHANISTAN             1235
> Joe      456 5th Ave   ANDORRA             8764
> Susie    789 Hwy 6    ALGERIA            5468
> 
> Does this make sense?
> 
> I want to put the info in Personal.xls because I only run the macro on
> this computer. All my macros are in that workbook, so mobility isn't
> really an issue.
> 
> Thanks for your help, it is greatly appreciated!
> 
> Austin
> 
> Pete_UK wrote:
> > You could use VLOOKUP to do this - check out Excel Help, or post more
> > details of your actual columns if you want help here.
> >
> > I'm not sure why you have put the data in Personal.xls - if you use
> > VLOOKUP to a different file, you will need to include the full path and
> > filename, and I think Personal.xls can exist in different locations.
> > Why not set up a folder directly in C:, eg C:\Ref_data and then put the
> > file in this folder - then you will always know the path to it.
> >
> > Hope this helps.
> >
> > Pete
> >
> > admailtx@gmail.com wrote:
> > > The list contains abbreviations and the corresponding word it belongs
> > > too. It is a list of countries and Postal abbreviations. I want to use
> > > that list to search for abbreviations in another file/sheet and replace
> > > it with the full word. For example, fie.xls has a list of people with
> > > foreign addresses. Personal.xls, the macro workbook, contains a sheet
> > > named abbrevaitions. I want the macro to search for the abbreviation in
> > > file.xls, and replace it with the data from personal.xls!abbrevaitions.
> > >
> > >
> > > Pete_UK wrote:
> > > > I don't quite understand - can't you just highlight column A and delete
> > > > it?
> > > >
> > > > Pete
> > > >
> > > > admailtx@gmail.com wrote:
> > > > > bump

-- 

Dave Peterson
0
petersod (12004)
8/1/2006 10:05:09 PM
Reply:

Similar Artilces:

Can I create a saveas macro that uses a date as the filename
I have created a spreadsheet with a date formula, which will be saved as a template. The users of this spreadsheet will be limited as to what they can and can't enter and the end result needs to be something very simple to use. I want to add a macro that will allow them to 'saveas' rather than overwrite an original document. This document will change everyday and will be identifiable by the date, therefore I'd like the filename to default to 'todays date'. If this field is contained within the spreadsheet, can this somehow be specified in the saveas box?? You co...

sqlserver to mysql data transfer of image column
we are copying a sqlserver 2005 data to mysql using perl dbd (freetds for sql server and standard dbd/dbi for perl). We are having problems with image column of a sql server table. this contains jpeg data and the application can not view it on mysql side. We also used bcp of sqlserver and same problem there too. is there anything that needs to be done on perl or sqlserver or mysql. thanks >>>>> "D9" == DBA 9999 <dba.9999@gmail.com> writes: D9> we are copying a sqlserver 2005 data to mysql using perl dbd D9> (freetds for sql server and st...

Pivot table
Is there a way in pivot tables after selecting hide detail for a column that new values are also hidden? ...

Problem using GO TO function in a macro on a large workbook.
Using Excel 97 on Windows XP. (This workbook was originally created in Excel 4.0.) I use a large excel workbook to do construction estimating. The WB consists of a main sheet that contains BUTTONS that were created and assigned to macros on a master macro sheet in the WB. The buttons take you to the specific section of another worksheet that contains the cells that return the total on the row corresponding to the button. Those sections are NAMED RANGES. The macro consists simply of: =FORMULA.GOTO(Sheet & Named Range) =SELECT ("RC3") The NAMED RANGES are created by going IN...

Chart size in relation to column width
Hi, I'm working with the Excel 2007 object model. I am trying to create an output sheet where i can position data and cells side by side in a specific order. The problem i am having is when trying to relate column widths with chart objects. I want to be able to get the size of the plot area and then size a cell or multiple cells based on this value. I then plan to move the chart into a position over the columns so that data above and below the chart lines up as i want it. How do the column sizes relate to the chart sizing? Any help on this would be greatly appreciated. G Hi, You...

If B2 = anything in column C, add A1
What would the proper function for this be? I thought I could use sumproduct but I'm not getting what I expected. Forget. Two seconds after posting I figured it out. "Carpie" wrote: > What would the proper function for this be? I thought I could use sumproduct > but I'm not getting what I expected. I think this is what you want.... This function will output the value in A1 if the value in B2 appears in ANY text string in column C. =IF(COUNTIF(C:C,"*"&B2&"*")>0,A1,"") Does that help? -- Regards, Dave "Carpie...

Changing column and row ID's
I have about 5 spreadsheets with 60 rows and 30 columns of data in each sheet (mostly numbers). I would would like to copy this data in a landscape format (imagine visually as rotating the data 90 degrees counter clockwise - not transposing the rows and columns using pastespecial -> transpose). u could imagine it as swapping the row id's with column id's .is it possible to do this in excel.if not is it possible to copy it in word and then rotate the spreadsheet. i hope i am clear. thanks So you want the value from AD1 to end up in A1 and that from A1 to end up in A30. AD2 end...

A way to detect macros? (not document macros)
We're writing an application that will be particularly vulnerable to attacks by macro programs (you know those apps that open up your app and type in text, etc..) Is there a way to detect their behaviour and perhaps block them? Thanks, Roger Short answer: No. Make sure you have good security, good AV, good anti spyware s/w and do not run with privilege. There are so many ways for such macros / spyware apps to operate that to attempt block them is an exercise in futility. The types of things that can happen include WM_SETTEXT / WM_GETTEXT (WM's in general) calls across processes, ...

Macro Protection #2
Hi I have a protected spreadsheet but can still get in to edit the Macros used. How do I stop this? Cheers Brian In VBA Tools ->VBA Project Properties and then the "Protection" Tab ...

Columns showing numerically
My columns are now listed numerically instead of by alpha characters. How do I get my alpha columns back? Select Tools Options, then select the General tab and uncheck the optio R1C1 refernce style click on O -- Gary Brow ----------------------------------------------------------------------- Gary Brown's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1708 View this thread: http://www.excelforum.com/showthread.php?threadid=38832 Tools | Options; open the General tab; locate the box "R1C1 reference style" and click to add check mark best wishes...

Austin Air Healthmate Replacement Filter w/ Prefilter
Price:$179.99 Image: http://thediscountguru.info/image.php?id=B00022UIMG Best deal: http://thediscountguru.info/index.php?id=B00022UIMG Replacement HEPA filter, prefilter, & carbon / zeolite mixture for Austin Air Healthmate (HM400) air purifier. SIMILAR PRODUCTS: Austin Air Healthmate Jr Replacement Filter w/ Prefilter - White:http://thediscountguru.info/index.php?id=B00022UINA Bird by Bird: Some Instructions on Writing and Life:http://thediscountguru.info/index.php?id=0385480016 ...

How Can I call A Macro From One "Work book" to Another "Work book"?
Hi, All... I would like to call a macro from one Workbook to another Workbook What i Mean to say .. for example Let I have To Workbooks , name workbook1 and Workbook2 In sheet1 of workbook1 i have data like in the range A1:D4 A B C D X 2 3 4 Y 3 4 5 Z 4 5 6 I selected specific range mentioned above(A2:D4) and selected th columns graph after that i deleted all the data , cut that graph and paste in anothe "sheet2" , so that what ever the reflections/changes made by me in dat range from the worksheet1 , occured in Graph of worksheet successfully. I r...

Sorting columns with expressions
I have a column that says =if(a1=b1,True, False) and want to sort the column to pull the true values Yet the sort doesn't do anything, is there a special way to sort these types of columns? Hi You need to include Columns A, B and C in your sort, not just column C. Then it will sort to bring all the False s together, followed by the True's Equally, you could mark the data and do Data>Autofilter and use the dropdown on column C to select True Regards Roger Govier et wrote: >I have a column that says =if(a1=b1,True, False) >and want to sort the column to pull the tru...

macro for Tab colour change
This is what i have but the problem is when i type something in cell the Tab changes colour this is good & when i clear cell i want Tab to go back to its original colour is this possible Private Sub Workbook_SheetChange(ByVal Sh As Object, _ ByVal Target As Range) If Intersect(Target, Range("B300")) Is Nothing Then Exit Sub ActiveSheet.Tab.ColorIndex = 15 End Sub You could try this... Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) If Intersect(Target, Range("B300")) Is Nothing Then ActiveSh...

Drop down on every Line for a specific column?
Hi, I am entering data into a spreadsheet and then saving it as a .CSV fil so I can upload it to my site and minipulate the data. For example I have fields (column's) "ID", "Manufacturer", "Model" as per picture below. I want to have a list of Manufacturers in drop-down so I can eliminate spelling errors ect. But I want to hav the dropdown on every line as I go down... Can someone help me +---------------------------------------------------------------- | Attachment filename: sheet.jpg |Download attachment: http...

pasting ranges, but not with hidden columns or rows.
Hi Am wondering if there is a way of copying and pasting a worksheet, but without pasting the hidden cells or groups. Thanks Tim Select your range Edit|goto|special|visible cells only Edit|copy to the other worksheet edit|paste Tim wrote: > > Hi Am wondering if there is a way of copying and pasting a worksheet, but > without pasting the hidden cells or groups. > > Thanks Tim -- Dave Peterson ec35720@msn.com ...

Double data value on a Calculated Column
Hi, I have a problem on my farm where calculated columns that are converted to a format other than single line of text. In the example below we have a numeric value (Estimate Infrastructure), calculated column with a calculation of '=270*[Estimate Infrastructure]' which is converted to a single line of text and then the same calculation again in the column 'Value Test2', but converted to currency. Has anyone seen this issue before? <file://62.173.65.162/calculatedvalueproblem.jpg> We are only MOSS 2007 SP1 so I am wondering if anyone has seen this b...

Locking Code In Individual Macros
Is there a way to lock one macro for viewing without affecting all macros in the workbook? I need to protect the Workbook_Open macro but the user needs to be able to view other macros for troubleshooting. If not, is there a way to change the font color on only one macro? Thanks Dear Carl: Why don't you put all your "protected code" in a Sub that will be stored in a protected module? That way you can call the Sub from the Workbook_Open event. Hope this helps. Please rate this post. G.Morales "Carl Bowman" wrote: > Is there a way to lock one macro for viewi...

format an entire column to contain checkbox activex control
I learned out how to insert one checkbox, but how do I insert them down an entire row - copy paste paste special does not seem to work.. Hi Blunoob, Try: '=============>> Public Sub Tester01() Dim SH As Worksheet Dim rng As Range Dim rCell As Range Set SH = ActiveSheet '<<==== CHANGE Set rng = SH.Range("A1:A100") '<<==== CHANGE Application.ScreenUpdating = False For Each rCell In rng With SH.CheckBoxes.Add(rCell.Left + 5, _ rCell.Top - 2, 5, 5) ...

Creating list based on Values from a second column
For example, Say I have a lot of coins and want to see from what Mints I have what coins. In Column B I have Mints In Column C I have the coin denomination. for each each row say, B2: Denver, C2: .25 B3: Phili, C3: .5 B3: Denver, C2: .10 etc etc I would like to create a unique list based on each mint. So i would have a column for Denver, column for Phili, etc. Each column does not have to filter out unique values. Help!? Thanks! It sounds like a nice layout to learn about pivottables. Add headers to row 1 (if you don't have them). I used Mints and Coin as my headers. Then...

Would like better understanding of how this macro works.
I found most of the macro online and made some changes, but I'm not sure of all of the functions being done. The macro lets you select a group of graphic files and then inserts them into a Word table with one graphic per row. I would appreciate comments on what the different parts of the macro do. Sub AddPix() Dim fd As FileDialog ActiveDocument.Tables.Add Range:=Selection.Range, NumRows:=1, NumColumns:= _ 2, DefaultTableBehavior:=wdWord9TableBehavior, AutoFitBehavior:= _ wdAutoFitContent With Selection.Tables(1) .Columns.Pre...

=?UTF-8?B?UmVtaW5kZXIgLSBNaWNyb3NvZnQgUmVzcG9uZHMgdG8gdGhlIEV2b2x1dGlvbiBvZiBDb21tdW5pdHk=?= #2
What is Happening? This message is to inform you that Microsoft will soon begin discontinuing newsgroups and transitioning users to Microsoft forums. Why? As you may know, newsgroups have existed for many years now; however, the traffic in the Microsoft newsgroups has been steadily decreasing for the past several years while customers and participants are increasingly finding solutions in the forums on Microsoft properties and third party sites. This move will unify the customer experience, centralize content, make it easier for active contributors to retain their influence, mitigate redund...

Excel 2007 Countifs macro with multiple criteria (OR)
Been working on this reporting macro for excel 2007. Cant figure out how to make countifs work for many possible values in one cell. I have report with multiple colums. With no problem I can check if call has been on hold for less than 26 seconds etc. But in the same countifs sentence I should check also if colum H cell has one of correct names from 50 possible names. There is something like 50 names and about 30 names should be counted and the other 20 not. So I think I need somekind of a OR sentence inside criteria (also saw one possible solution that there would be possib...

Enable/Disable Macros question
Hi I have a workbook that has a few macros in it which need to be enabled. How can i close the workbook if someone clicks 'disable macros'.? I would like to place the 'check' in the workbook open module if possible. Thanks Paul On Fri, 13 Feb 2004 18:19:05 -0000, "Paul Watkins" <paul.watkins4@ntlworld.com> wrote: >Hi > >I have a workbook that has a few macros in it which need to be enabled. >How can i close the workbook if someone clicks 'disable macros'.? > >I would like to place the 'check' in the workbook open modu...

Save sheet copy with cell value name using a macro.
Good afternoon All, I need a VB statement to save a copy of the sheet i'm using (same were the macro is executed) with a cell value (EG cell "B2") of the workbook I'm using into a predefined path (EG "C:\"). If anybody can help me with this I'll be very glad. Leo. You want to save a sheet as a workbook with a name of the value in Range("B2") of the sheet? Sub Make_New_Book() Dim w As Worksheet Application.ScreenUpdating = False Application.DisplayAlerts = False ActiveSheet.Copy ActiveWorkbook.SaveAs Filename:="C:\...