Hi

I am new to VBA (have gone through MS training 30 minutes). My
requirement is to check that UK post codes are in the correct format.


I want to create a macro for this and need help.

Below is what I have been doing. Notepad is used because copying from
Excel to word takes a very long time.

Quantity 50,000
I have been using a Heath Robinson Method as follows:
trim to remove any leading or training spaces then
Copy to Notepad
Copy to word
Remove all extraneous symbols ,./:;’\=-+ Etc
Replace double space with single space.
Copy to Notepad
Copy from Notepad to Excel
split using space as the delimiter into two columns 1st part 2nd Part
Take the first part
Count characters
Sort to bring to the top records with too many characters
Check high number of characters There should be no more than 4
Check the low number of characters as this could indicate a space was
in the wrong place
Manually check as this could indicate a space.
Copy to Notepad
Copy 1st part to word
Remove digits
Copy to Notepad
Copy from Notepad to Excel
Count characters
Sort to bring to the top records with too many characters
Check high number of characters There should be no more than 2 / 3
In the case of 3 manually look at the original Post Code and leave
Check the low number of characters as this could indicate a space was
in the wrong place
Manually check as this could indicate a space.
Revert to word1st part to word
Ctrl Z till the whole first part is visible.
Remove Letters Copy result to Notepad
Copy from Notepad to Excel
Trim
Count characters.
Sort
Manually check if characters are over 2 against initial code.
Repair those which are SW2X to SW2X
Using = & in excel create new column
Copy new column to itself using special paste and value to remove
formula.
ETC

Repeat the process on the 2ns part with slightly different criteria but
basically the same.

System prone to give bad results because so many manual actions leave
bits out etc.


Here are the post code variations:
UK Structure
• A1 2BC
• D34 5EF
• GH6 7IJ
• KL8M 9NO
I want to make a macro which does this for me.

I have located a java solution which is almost correct but not quite

The total length must be 6,7, or 8 characters, a gap (space character)
must be included
The inward code, the part to the right of the gap, must always be 3
characters
The first character of the inward code must be numeric
The second and third characters of the inward code must be alpha
The outward code, the part to the left of the gap, can be 2,3, or 4
characters
The first character of the outward code must be alpha
If the outward code is 3 characters then the last character must be
numeric (code not written).
If the outward code is 4 characters then the last character must be
alpha (code not written).

<SCRIPT LANGUAGE="JavaScript"

<!-- Begin
function postit(){ //check postcode format is valid
test = document.details.pcode.value; size = test.length
test = test.toUpperCase(); //Change to uppercase
while (test.slice(0,1) == " ") //Strip leading spaces
{test = test.substr(1,size-1);size = test.length
}
while(test.slice(size-1,size)== " ") //Strip trailing spaces
{test = test.substr(0,size-1);size = test.length
}
document.details.pcode.value = test; //write back to form field
if (size < 6 || size 8){ //Code length rule
alert(test + " is not a valid postcode - wrong length");
document.details.pcode.focus();
return false;
}
if (!(isNaN(test.charAt(0)))){ //leftmost character must be alpha
character rule
alert(test + " is not a valid postcode - cannot start with a
number");
document.details.pcode.focus();
return false;
}
if (isNaN(test.charAt(size-3))){ //first character of inward code must
be numeric rule
alert(test + " is not a valid postcode - alpha character in wrong
position");
document.details.pcode.focus();
return false;
}
if (!(isNaN(test.charAt(size-2)))){ //second character of inward code
must be alpha rule
alert(test + " is not a valid postcode - number in wrong
position");
document.details.pcode.focus();
return false;
}
if (!(isNaN(test.charAt(size-1)))){ //third character of inward code
must be alpha rule
alert(test + " is not a valid postcode - number in wrong
position");
document.details.pcode.focus();
return false;
}
if (!(test.charAt(size-4) == " ")){//space in position length-3 rule
alert(test + " is not a valid postcode - no space or space in wrong
position");
document.details.pcode.focus();
return false;
}
count1 = test.indexOf(" ");count2 = test.lastIndexOf(" ");
if (count1 != count2){//only one space rule
alert(test + " is not a valid postcode - only one space allowed");
document.details.pcode.focus();
return false;
}
alert("Postcode Format OK");
return true;
}
// End --
</script

Am I asking too much particularly as I am a new member and a newbie
generally

All the Best
Jacky Kenna


--
jacky Kenna
------------------------------------------------------------------------
jacky Kenna's Profile: http://www.excelforum.com/member.php...o&userid=31672
View this thread: http://www.excelforum.com/showthread...hreadid=513639