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
Medium Priority
Last Modified: 2014-11-04

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


Question by:andygen
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
LVL 24

Accepted Solution

Phillip Burton earned 2000 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.
LVL 24

Expert Comment

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

Author Comment

ID: 40421617
Hi Phillip

Great thanks, just what I needed :)

Thanks for the help.


Author Closing Comment

ID: 40421621
Quick solution to my problems, great.

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

762 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