Solved

How to create a macro that can find, copy, amend and paste cells. Plus add another cell next to that, that contains a place.

Posted on 2014-11-04
4
195 Views
Last Modified: 2014-11-04
HI,

Ok I have a spreadsheet that contains uk phone number information. I will attach it below, it lists multiple area codes with info about the phone companies that can use the numbers.

Basically the standard format of an area code is 5 digits starting with a 0 so 0XXXX. eg 01200.  However in column D the area codes are missing the leading 0. eg 1200 etc

I have created a new column B to show the correct area code value including the 0.

So for the start of the macro, I need it to go through the spreadsheet and copy each area code from column D to column B including adding the missing 0.

One point to note is that when I click on an area code in column D, it shows a ' in front of the are codes, which isn't visible unless looking at the cell in the formula bar. So not sure if this could effect the marco.

Next is that each area code corresponds to a location. I have listed these below, so I also want the marco to add the location to column a for each area code to. I have attached a spreadsheet with the are codes and places in each column.

01200 Clitheroe
01202 Bournemouth
01204 Bolton
01205 Boston
01206 Colchester
01207 Consett
01208 Bodmin
01209 Redruth, Cornwall
01223 Cambridge
01224 Aberdeen
01225 Bath
01226 Barnsley
01227 Canterbury
01228 Carlisle
01229 Barrow-in-Furness
01233 Ashford, Kent
01234 Bedford
01235 Abingdon
01236 Coatbridge
01237 Bideford
01239 Cardigan
01241 Arbroath
01242 Cheltenham
01243 Chichester, West Sussex
01244 Chester
01245 Chelmsford
01246 Chesterfield
01248 Bangor, Gwynedd
01249 Chippenham
01250 Blairgowrie
01252 Aldershot
01253 Blackpool
01254 Blackburn
01255 Clacton-on-Sea
01256 Basingstoke
01257 Coppull, Chorley
01258 Blandford
01259 Alloa
01260 Congleton
01261 Banff
01262 Bridlington
01263 Cromer
01264 Andover
01267 Carmarthen
01268 Basildon
01269 Ammanford
01270 Crewe
01271 Barnstaple
01273 Brighton
01274 Bradford
01275 Clevedon
01276 Camberley
01277 Brentwood
01278 Bridgwater
01279 Bishop's Stortford
01280 Buckingham
01282 Burnley
01283 Burton upon Trent
01284 Bury St Edmunds
01285 Cirencester
01286 Caernarfon
01287 Guisborough, Cleveland
01288 Bude
01289 Berwick-upon-Tweed
01290 Cumnock, Ayrshire
01291 Chepstow
01292 Ayr
01293 Crawley
01294 Ardrossan, Ayrshire
01295 Banbury
01296 Aylesbury
01297 Axminster
01298 Buxton
01299 Bewdley
01300 Cerne Abbas, Dorset
01301 Arrochar
01302 Doncaster
01303 Folkestone
01304 Dover
01305 Dorchester
01306 Dorking
01307 Forfar
01308 Bridport, Dorset
01309 Forres
01320 Fort Augustus
01322 Dartford
01323 Eastbourne
01324 Falkirk
01325 Darlington
01326 Falmouth
01327 Daventry
01328 Fakenham
01329 Fareham
01330 Banchory, Deeside
01332 Derby
01333 Peat Inn, Fife
01334 St Andrews, Fife
01335 Ashbourne, Derbyshire
01337 Ladybank, Fife
01339 Aboyne
01340 Craigellachie, Elgin
01341 Barmouth, Dolgellau
01342 East Grinstead
01343 Elgin
01344 Bracknell, Easthampstead
01346 Fraserburgh
01347 Easingwold
01348 Fishguard
01349 Dingwall
01350 Dunkeld
01352 Mold, Flint
01353 Ely
01354 Chatteris, March, Cambridgeshire, Fenland
01355 East Kilbride
01356 Brechin, Edzell
01357 Strathaven, East Kilbride
01358 Ellon
01359 Pakenham, Elmswell
01360 Killearn, Drymen
01361 Duns
01362 Dereham
01363 Crediton
01364 Ashburton, Devon
01366 Downham Market
01367 Faringdon
01368 Dunbar
01369 Dunoon
01371 Great Dunmow, Essex
01372 Esher
01373 Frome
01375 Grays Thurrock, Essex
01376 Braintree, Essex
01377 Driffield
01379 Diss
01380 Devizes
01381 Fortrose
01382 Dundee
01383 Dunfermline
01384 Dudley
01386 Evesham
01388 Bishop Auckland, Durham
01389 Dumbarton
01392 Exeter
01394 Felixstowe
01395 Budleigh Salterton, Exmouth
01397 Fort William
01398 Dulverton, Exmoor

Thanks

Andy
5digitareacodesmarcrotry.xls
places.xlsx
0
Comment
Question by:andygen
  • 2
  • 2
4 Comments
 
LVL 24

Accepted Solution

by:
Phillip Burton earned 500 total points
ID: 40421416
Please find attached.

The ' is Excel's indication that the cell contains something which it treats as text, instead of something which it treats as a number. That's why it's not visible in the cell itself, merely when you are editing it.
5digitareacodesmarcrotry---Answer.xls
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40421417
By the way, none of this was a macro - it was two formulas instead.
0
 

Author Comment

by:andygen
ID: 40421617
Hi Phillip

Great thanks, just what I needed :)

Thanks for the help.

Andy
0
 

Author Closing Comment

by:andygen
ID: 40421621
Quick solution to my problems, great.
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
This article is the result of a quest to better understand Task Scheduler 2.0 and all the newer objects available in vbscript in this version over  the limited options we had scripting in Task Scheduler 1.0.  As I started my journey of knowledge I f…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now