COLUMN Find/Replace Macro?

[GUIDO22]

I have a spreadsheet with a column containing '1...n' race meetings.
These are all 4/5 letter abbreviations - but I need the full name in the column. I would like a macro/routine to iterate through the column replacing the abbreviation with its full name equivalent. I would welcome ideas on how to write this please.
Thank you.

race name examples :
WOLV = WOLVERHAMPTON
CHELT = CHELTENHAM
SOUTH =SOUTHWELL

 

[Brianwarnock]

It is probably quicker and easier not to bother writing a macro especially if this is a one off.

On a new sheet create an array of abbreviation and full name! Sorted in ascending order for the abbreviated name

Ie sheet2 a1 Cheltenham b1 Cheltenham

Etc

Then Insert a new col A on the original sheet and let's assume 20 courses, enter
= Vlookup(B1,Sheet2!A$1:B$20,2)
Assuming first abbreviation now in B1
Fill down
Copy your new col A which will show full names
Past especial values over your abbreviated names
Delete col A

Job done

Brian

 

[GUIDO22]

>>It is probably quicker and easier not to bother writing a macro especially if this is a one off.

Problem is - this is NOT a one off - will need to run DAILY.
Plus, the list of race track names is quite extensive and will have to be added to as I go for the first few weeks at least.....

Any other suggestions please?

 

[Brianwarnock]

Whatever approach is used you are going to need a table to convert the abbreviation to the full name and thus are unable to avoid the creation and upkeep of that.
Keep it in another workbook, which I think you can address directly in the Vlookup but I forget the syntax, or simply copy it in for the run.

The manual effort to do what I suggested is less than 5 minutes.

Brian

 

[Brianwarnock]

I haven't got access to excel at the moment but I think that a macro called fullname would approximate to the following

Dim mycell as range
Dim searchcell as range
Dim r1 as long
Dim r2 as long
R1=3
R2=1
set mycell = sheets("sheet1").cells(r1,1)
Set searchcell = sheets("sheet2").cells(r2,1)
Do until mycell is null
If mycell <>searchcell then
R2=r2+1
Else
Fullname = searchcell.offset(0,1)
R2=1
R1=r1+1
End if
Loop


Edit the above code is rubbish falling between a function and a sub.
lesson - never attempt to code late at night after several malts.

 

[GUIDO22]

I haven't got access to excel at the moment but I think that a macro called fullname would approximate to the following

Dim mycell as range
Dim searchcell as range
Dim r1 as long
Dim r2 as long
R1=3
R2=1
set mycell = sheets("sheet1").cells(r1,1)
Set searchcell = sheets("sheet2").cells(r2,1)
Do until mycell is null
If mycell <>searchcell then
R2=r2+1
Else
Fullname = searchcell.offset(0,1)
R2=1
R1=r1+1
End if
Loop

I do not follow - and would ask that if you are able to submit a sample spreadsheet - I am not too familiar with coding in Excel at all.

Presently, I am using find/Replace each day to convert the Abbr / Full names which currently takes me less than 5 minutes BUT as this is a daily operation, I wish to effect the operation with one button press ONLY and do it in a few seconds!

I figured I could simply have a VB module attached to the s/sheet which itemised all the abbreviations (once these are listed, there is no further upkeep required). A one pass coding routine would work through each cell and replace the abbreviation with the full name.

Anyone know of a way to do this please?

TIAFYH

 

[Brianwarnock]

How can the module convert the abbreviation to the full name without a conversion table, unless it is hard coded which would be a nightmare to upkeep.

Brian

 

[Brianwarnock]

I attach spread sheet'
right click tab sheet1, then view code to see sub.

It does use a conversion table on sheet2 as I know of no other way.

Brian

 

[GUIDO22]

You seem to know more about this application than I .... !
Why would this be (in your words) a 'nightmare' to upkeep...?
I anticipate adding the new course names as I get them each week. 3 or 4 at a time.... that wont be too much effort....!

The compilation of all names as I have mentioned would only take a month or two. But once done, that's it.

 

[GUIDO22]

Brian
Thanks for the sample sheet - just what I wanted....!
Thank you very much,

Regards
Guy

 

[Brianwarnock]

Just checked
the conversion table can be in a separate workbook, this must be open at the time of running the sub, to do this change the ref to Sheet2 to for example

Workbooks("conversiontable.xls").Sheets("sheet1").Cells(r2,1) or as required

Brian

 

[Brianwarnock]

The nightmare I was talking about was if the conversion was in the code like for example
If a = chelt then
a = cheltenham
elseif a=
etc

Brian

 

[GUIDO22]

The nightmare I was talking about was if the conversion was in the code like for example
If a = chelt then
a = cheltenham
elseif a=
etc

Brian

I get you... yes, agreed it would be. Your suggestion is by far the simplest! Wont need to run in a seperate worksheet either as the selections I use are pasted into my 'template' workbook which will contain the lookup table. From here I run the update and then save as the selections as CSV for use elsewhere...
Thanks again!