Postcode Macro - Amending

[keilah]

Hi Not sure on how to do this.....

But need to amend the following code so if the Postcode is 6 (letters and number, do the split 3 and 3 i.e. B21 9HX) if the post code is 7 (then always 4 and three)..........and keeping the Upper case format.....


here is the original code

[VBA]
Sub PostcodeMacro()
Dim ColNr As Long
ColNr = InputBox("Enter Column Number to be checked")
RowsA = Cells(Rows.Count, ColNr).End(xlUp).Row
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For i = 1 To RowsA
If Len(Cells(i, ColNr)) = 7 And InStr(1, Cells(i, ColNr), " ") <> 3 Then
TempVal = Replace(Cells(i, ColNr), " ", "")
Cells(i, ColNr) = UCase(Left(TempVal, 3)) & " " & UCase(Right(TempVal, 3))
Else
Cells(i, ColNr) = UCase(Cells(i, ColNr))
End If
Next i
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub
[/VBA]

thanks

[Bob Phillips]

[vba]

Sub PostcodeMacro()
Dim ColNr As Long
Dim RowsA As Long
Dim i As Long
Dim TempVal

ColNr = InputBox("Enter Column Number to be checked")
RowsA = Cells(Rows.Count, ColNr).End(xlUp).Row

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

For i = 1 To RowsA
TempVal = Replace(Cells(i, ColNr).Value, " ", "")
If Len(TempVal) = 6 Then
Cells(i, ColNr).Value = UCase(Left(TempVal, 3) & " " & Right(TempVal, 3))
ElseIf Len(TempVal) = 7 Then
Cells(i, ColNr).Value = UCase(Left(TempVal, 4) & " " & Right(TempVal, 3))
Else
Cells(i, ColNr).Value = UCase(TempVal)
End If
Next i

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub
[/vba]

[keilah]

thanks for the feedback......ok i see for future referrence.

[TonyJollans]

UK Postcodes always end in a three-character inbound code in the format number-letter-letter. Wouldn't it make more sense to always put the space three characters from the right regardless of length? That way, for example, W14AA would be caught as well.

[Bob Phillips]

I don't recall them now, but I believe that there are a few that don't follow this rule. There have been many threads (interminably boring) on postcode validation.

[TonyJollans]

Last time I worked with them (about 6 years ago) there was the one exception - GIR OAA - that had existed from day one. They've probably all changed now