jindon Wrote:
assuming the data starts from A1
result will be col B


Code:
--------------------

Sub test()
Dim a, i As Long, ii As Long
Dim txt As String, x, result()
With ActiveSheet
a = .Range("a1").CurrentRegion.Value
For i = 1 To UBound(a, 1)
If InStr(a(i, 1), "GP") 0 Then
a(i, 1) = _
Trim(Replace(Replace(Replace(a(i, 1), "GP", ""), "!", ""), ".", " "))
x = Split(a(i, 1))
If Val(x(3)) 999 Then x(3) = _
Application.Round(Val(x(3)) / 10, -2)
If Val(x(3)) = 1000 Then x(3) = 990
If Val(x(7)) 999 Then x(7) = _
Application.Round(Val(x(7)) / 10, -2)
If Val(x(7)) = 1000 Then x(7) = 990
txt = "NO" & x(0) & "." & x(1) & "." & x(2) & "." & Val(x(3)) & _
" W" & x(4) & "." & x(5) & "," & x(6) & "." & Val(x(7))
If InStr(a(i + 1, 1), "GP") 0 Then
a(i + 1, 1) = _
Trim(Replace(Replace(Replace(a(i + 1, 1), "GP", ""), "!", ""), ".", " "))
x = Split(a(i, 1))
If Val(x(3)) 999 Then x(3) = _
Application.Round(Val(x(3)) / 10, -2)
If Val(x(3)) = 1000 Then x(3) = 990
If Val(x(7)) 999 Then x(7) = _
Application.Round(Val(x(7)) / 10, -2)
If Val(x(7)) = 1000 Then x(7) = 990
txt = txt & Chr(32) & _
"NO" & x(0) & "." & x(1) & "." & x(2) & "." & Val(x(3)) & _
" W" & x(4) & "." & x(5) & "," & x(6) & "." & Val(x(7))
ii = ii + 1: ReDim Preserve result(ii): result(ii) = txt: txt = Empty
i = i + 1
End If
End If
Next
Erase a
.Range("b1").Resize(UBound(result)) = Application.Transpose(result)
Erase result
End With
End Sub

--------------------



Ron,

I tried running this and I get the following:

Runtime Error #9
Subscript out of Range

Mike C


--
catalfamo1220
------------------------------------------------------------------------
catalfamo1220's Profile: http://www.excelforum.com/member.php...o&userid=29458
View this thread: http://www.excelforum.com/showthread...hreadid=491671