Macro Replace Column A with Column B

[admailtx]

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

 

[admailtx]

bump

 

[Pete_UK]

I don't quite understand - can't you just highlight column A and delete
it?

Pete

 

[admailtx]

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]

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]

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

 

[Dave Peterson]

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