PDA

View Full Version : Macro for adding and verifiyng country code phone number(prefix)



Brever
08-13-2015, 02:18 AM
Hello everyone,


I am new on the forum and new to the vba coding, to be honest, a couple of classes in college and that`s it


The thing is that I receive daily files with the wrong country code phone(prefix) or missing and I need the macro to add it by default because I have hundreds of entries.


I have my phone no`s in column K and I need the macro to first verify the country phone code based on the country in column J and if the number starts with the correct country phone code, move to the next row, if it doesn`t, it should add it. I need to mention that I will always have the phone number and the specific country on the same row.


I have here a code that only adds a country predefined value to the cell but having no reference to the country, it doesn't look for the country information to add it depending on the specific country.


Many thanks in advance, I really appreciate any suggestion


[ForEach r In Range("K2:K")IfNot(Left(r.Value,2)="44")Then
r.Value ="44"& r.Value
EndIf
Next r]

andrew93
08-15-2015, 12:11 AM
Hello and welcome to VBAX.

You could make this much easier if you had a table of countries and country codes within your spreadsheet. This information is required whether or not a macro is used. Once you have that table of information, you can easily resolve this using formulas instead of a macro. You would use the first 2 non-zero characters in a lookup function to test the country returned by the lookup is the same as the country from the same row. If not, append the appropriate country code.