keilah
10-02-2007, 01:29 AM
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
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
thanks
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
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
thanks