Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to allocate movement if expected last record is missing
Hi,
I'm not an accomplished programmer so please bear with me. Here is very rudimentary code I have written. No doubt there are better ways of doing this. However I ran this on a sample file and it does what I want it to do.However I need some help to incorporate an aspect I cannot seem to get right. Input File Sample Tline, 8, 11, Tline, 386, 16 10123567.01,YP_TRX_PREV 10123567.01,YP_TRX_CURR 10123568.01,YP_TRX_PREV 10123568.01,YP_TRX_INCR 10123569.01,YP_TRX_PREMINV 10123569.01,YP_TRX_CURR Would like to see this 10123567.01 YP_TRX_PREV, PREV 10123567.01 YP_TRX_CURR, CURR 10123568.01 YP_TRX_PREV, PREV 10123568.01 YP_TRX_INCR, OFF 10123569.01 YP_TRX_PREMINV, PREMINV 10123569.01 YP_TRX_CURR, CURR Dim Tline As String Dim SegStr As String Dim i As Long Dim TrxStr, PolStr as string Line Input #1, Tline InitPolRef = Mid$(Tline, 8, 11) 'eg 100123567.01 TrxStr = Mid$(Tline, 386, 16) 'First Record If TrxStr < "YP_TRX_PREV " Then TrxStr = "NBUS" Else TrxStr = Right(TrxStr, 9) End If SegStr = Mid$(Tline, 1, 2) & Mid$(Tline, 3, 2) & Mid$(Tline, 5, 2) & Mid$(Tline, 7, 12) & Mid$(Tline, 24, 9) & Mid$(Tline, 35, 2) & Mid$(Tline, 37, 2) & Mid$(Tline, 39, 2) & Mid$(Tline, 41, 2) Print #2, SegStr & "," & TrxStr 'Balance of Records i = 2 Do Until EOF(1) Line Input #1, Tline PolStr = Mid$(Tline, 8, 11) TrxStr = Mid$(Tline, 386, 16) 'If 2nd record(Polstr) = 1st record(InitPolRef) If InitPolRef = PolStr Then TrxStr = Right(TrxStr, 9) 'If 2nd record(Polstr) < 1st record(InitPolRef) Else PolStr = Mid$(Tline, 8, 11) InitPolRef = PolStr If TrxStr < "YP_TRX_PREV " Then TrxStr = "NBUS" Else TrxStr = "PREV" End If End If SegStr = Mid$(Tline, 1, 2) & Mid$(Tline, 3, 2) & Mid$(Tline, 5, 2) & Mid$(Tline, 7, 12) & Mid$(Tline, 24, 9) & Mid$(Tline, 35, 2) & Mid$(Tline,37, 2) & Mid$(Tline, 39, 2) & Mid$(Tline, 41, 2) Print #2, SegStr & "," & TrxStr i = i + 1 Loop Close #1 Close #2 MsgBox ("End of Run: ") + Str(i - 1) + " records processed" I need to put in an "OFF" movement when the LAST record of a series is NOT "YP_TRX_CURR" i.e 10123568.01,YP_TRX_PREV 10123568.01,YP_TRX_INCR Result 10123568.01 YP_TRX_PREV, PREV 10123568.01 YP_TRX_INCR, OFF If it helps the file is sorted by "Tline, 8, 11" field and there is a date associated with each "Tline, 386, 16" field. Hope I have not irritated people by this long-winded explanation. Can anyone help please, please, please...? Thanks Hilton |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
find a record with a missing value | Excel Discussion (Misc queries) | |||
Simple calc not working as expected - what am I missing? | Excel Discussion (Misc queries) | |||
How many days the ready stock+expected stock will last as allocate | Excel Discussion (Misc queries) | |||
Identify missing record numbers | Excel Worksheet Functions | |||
Identify missing record numbers | Excel Discussion (Misc queries) |