MartinS
Active Member
- Joined
- Jun 17, 2003
- Messages
- 487
- Office Version
- 365
- Platform
- Windows
Following on from this thread (http://www.mrexcel.com/board2/viewtopic.php?t=91955), I have now tidied up the existing UDFs, which has now caused two of the functions (both which use the same data file) to go wrong. The problem is that each line of the data file read contains a year and then 12 individual numbers, each comma delimited, but only until the last year, which has whatever the latest number available is, so for example, at this time, it has the year and the first 5 numbers only, so my following code falls over when it gets to the latest year as there are not 13 delimited fields.
I had 'on error resume next' at the start, which in this case made it work, but by removing that, it now falls over. Would it be easier to simply reinstate the error handling or is there a better solution to deal with the last line of the file. I guess i could use Line Input and split the data out, but am not sure how best to do this, so if anyone can help with the code to split out a delimited line, that may be the answer.
For Info, this function is looking for the latest year where there is a non-zero value on September.
My code is as follows:
The data file looks like this:
1947, 0.0, 0.0, 0.0, 0.0, 0.0, 7.3, 7.4, 7.3, 7.4, 7.4, 7.6, 7.6
1948, 7.6, 7.8, 7.8, 7.9, 7.9, 8.1, 7.9, 7.9, 7.9, 7.9, 8.0, 8.0
1949, 8.0, 8.0, 8.0, 8.0, 8.1, 8.1, 8.1, 8.1, 8.2, 8.2, 8.2, 8.3
1950, 8.3, 8.3, 8.3, 8.4, 8.4, 8.4, 8.4, 8.3, 8.4, 8.4, 8.5, 8.5
1951, 8.6, 8.6, 8.7, 8.9, 9.1, 9.2, 9.2, 9.3, 9.4, 9.5, 9.5, 9.5
1952, 9.7, 9.8, 9.8, 9.9, 9.9, 10.1, 10.1, 10.0, 10.0, 10.1, 10.1, 10.1
1953, 10.2, 10.2, 10.3, 10.3, 10.3, 10.3, 10.3, 10.3, 10.3, 10.3, 10.3, 10.3
1954, 10.3, 10.3, 10.3, 10.4, 10.3, 10.4, 10.6, 10.6, 10.5, 10.6, 10.6, 10.6
1955, 10.7, 10.7, 10.7, 10.8, 10.8, 11.0, 11.0, 10.9, 11.0, 11.2, 11.3, 11.3
1956, 11.3, 11.3, 11.4, 11.6, 11.5, 11.5, 11.5, 11.5, 11.5, 11.6, 11.6, 11.6
1957, 11.7, 11.7, 11.7, 11.8, 11.8, 11.9, 12.0, 12.0, 11.9, 12.0, 12.1, 12.2
1958, 12.2, 12.1, 12.2, 12.3, 12.3, 12.4, 12.2, 12.2, 12.2, 12.3, 12.3, 12.4
1959, 12.4, 12.4, 12.4, 12.3, 12.3, 12.3, 12.3, 12.3, 12.2, 12.3, 12.4, 12.4
1960, 12.4, 12.4, 12.3, 12.4, 12.4, 12.5, 12.5, 12.4, 12.4, 12.5, 12.6, 12.6
1961, 12.6, 12.6, 12.7, 12.8, 12.8, 12.9, 12.9, 13.0, 13.0, 13.0, 13.2, 13.2
1962, 13.2, 13.2, 13.3, 13.4, 13.5, 13.6, 13.5, 13.4, 13.4, 13.4, 13.5, 13.5
1963, 13.6, 13.7, 13.7, 13.7, 13.7, 13.7, 13.7, 13.6, 13.7, 13.7, 13.7, 13.8
1964, 13.8, 13.8, 13.9, 14.0, 14.1, 14.2, 14.2, 14.2, 14.2, 14.3, 14.4, 14.4
1965, 14.5, 14.5, 14.5, 14.8, 14.9, 14.9, 14.9, 14.9, 14.9, 15.0, 15.0, 15.1
1966, 15.1, 15.1, 15.1, 15.3, 15.4, 15.5, 15.4, 15.5, 15.5, 15.5, 15.6, 15.6
1967, 15.7, 15.7, 15.7, 15.8, 15.8, 15.8, 15.7, 15.7, 15.7, 15.8, 15.9, 16.0
1968, 16.1, 16.1, 16.2, 16.5, 16.5, 16.6, 16.6, 16.6, 16.6, 16.7, 16.8, 17.0
1969, 17.1, 17.2, 17.2, 17.4, 17.4, 17.5, 17.5, 17.4, 17.5, 17.6, 17.6, 17.8
1970, 17.9, 18.0, 18.1, 18.4, 18.4, 18.5, 18.6, 18.6, 18.7, 18.9, 19.0, 19.2
1971, 19.4, 19.5, 19.7, 20.1, 20.3, 20.4, 20.5, 20.5, 20.6, 20.7, 20.8, 20.9
1972, 21.0, 21.1, 21.2, 21.4, 21.5, 21.6, 21.7, 21.9, 22.0, 22.3, 22.4, 22.5
1973, 22.6, 22.8, 22.9, 23.3, 23.5, 23.7, 23.8, 23.8, 24.0, 24.5, 24.7, 24.9
1974, 25.3, 25.8, 26.0, 26.9, 27.3, 27.6, 27.8, 27.8, 28.1, 28.7, 29.2, 29.6
1975, 30.4, 30.9, 31.5, 32.7, 34.1, 34.8, 35.1, 35.3, 35.6, 36.1, 36.6, 37.0
1976, 37.5, 38.0, 38.2, 38.9, 39.3, 39.5, 39.6, 40.2, 40.7, 41.4, 42.0, 42.6
1977, 43.7, 44.1, 44.6, 45.7, 46.1, 46.5, 46.6, 46.8, 47.1, 47.3, 47.5, 47.8
1978, 48.0, 48.3, 48.6, 49.3, 49.6, 50.0, 50.2, 50.5, 50.7, 51.0, 51.3, 51.8
1979, 52.5, 53.0, 53.4, 54.3, 54.7, 55.7, 58.1, 58.5, 59.1, 59.7, 60.3, 60.7
1980, 62.2, 63.1, 63.9, 66.1, 66.7, 67.4, 67.9, 68.1, 68.5, 68.9, 69.5, 69.9
1981, 70.3, 70.9, 72.0, 74.1, 74.6, 75.0, 75.3, 75.9, 76.3, 77.0, 77.8, 78.3
1982, 78.7, 78.8, 79.4, 81.0, 81.6, 81.9, 81.9, 81.9, 81.9, 82.3, 82.7, 82.5
1983, 82.6, 83.0, 83.1, 84.3, 84.6, 84.8, 85.3, 85.7, 86.1, 86.4, 86.7, 86.9
1984, 86.8, 87.2, 87.5, 88.6, 89.0, 89.2, 89.1, 89.9, 90.1, 90.7, 91.0, 90.9
1985, 91.2, 91.9, 92.8, 94.8, 95.2, 95.4, 95.2, 95.5, 95.4, 95.6, 95.9, 96.0
1986, 96.2, 96.6, 96.7, 97.7, 97.8, 97.8, 97.5, 97.8, 98.3, 98.5, 99.3, 99.6
1987,100.0,100.4,100.6,101.8,101.9,101.9,101.8,102.1,102.4,102.9,103.4,103.3
1988,103.3,103.7,104.1,105.8,106.2,106.6,106.7,107.9,108.4,109.5,110.0,110.3
1989,111.0,111.8,112.3,114.3,115.0,115.4,115.5,115.8,116.6,117.5,118.5,118.8
1990,119.5,120.2,121.4,125.1,126.2,126.7,126.8,128.1,129.3,130.3,130.0,129.9
1991,130.2,130.9,131.4,133.1,133.5,134.1,133.8,134.1,134.6,135.1,135.6,135.7
1992,135.6,136.3,136.7,138.8,139.3,139.3,138.8,138.9,139.4,139.9,139.7,139.2
1993,137.9,138.8,139.3,140.6,141.1,141.0,140.7,141.3,141.9,141.8,141.6,141.9
1994,141.3,142.1,142.5,144.2,144.7,144.7,144.0,144.7,145.0,145.2,145.3,146.0
1995,146.0,146.9,147.5,149.0,149.6,149.8,149.1,149.9,150.6,149.8,149.8,150.7
1996,150.2,150.9,151.5,152.6,152.9,153.0,152.4,153.1,153.8,153.8,153.9,154.4
1997,154.4,155.0,155.4,156.3,156.9,157.5,157.5,158.5,159.3,159.5,159.6,160.0
1998,159.5,160.3,160.8,162.6,163.5,163.4,163.0,163.7,164.4,164.5,164.4,164.4
1999,163.4,163.7,164.1,165.2,165.6,165.6,165.1,165.5,166.2,166.5,166.7,167.3
2000,166.6,167.5,168.4,170.1,170.7,171.1,170.5,170.5,171.7,171.6,172.1,172.2
2001,171.1,172.0,172.2,173.1,174.2,174.4,173.3,174.0,174.6,174.3,173.6,173.4
2002,173.3,173.8,174.5,175.7,176.2,176.2,175.9,176.4,177.6,177.9,178.2,178.5
2003,178.4,179.3,179.9,181.2,181.5,181.3,181.3,181.6,182.5,182.6,182.7,183.5
2004,183.1,183.8,184.6,185.7
If you require any more info, then please ask.
Regards
Martin
I had 'on error resume next' at the start, which in this case made it work, but by removing that, it now falls over. Would it be easier to simply reinstate the error handling or is there a better solution to deal with the last line of the file. I guess i could use Line Input and split the data out, but am not sure how best to do this, so if anyone can help with the code to split out a delimited line, that may be the answer.
For Info, this function is looking for the latest year where there is a non-zero value on September.
My code is as follows:
Code:
Function GETLATESTRPIY(DateToLookFor As Date) As Integer
Const RPILogFile As String = "C:\RPI.DAT"
Dim IndValue(1 To 12) As Double
Dim IndicesDate As Integer, IndCounter As Integer, FileNo As Integer
FileNo = FreeFile()
Open RPILogFile For Input Access Read Shared As #FileNo
Do
Input #FileNo, IndicesDate, IndValue(1), IndValue(2), _
IndValue(3), IndValue(4), IndValue(5), IndValue(6), _
IndValue(7), IndValue(8), IndValue(9), IndValue(10), _
IndValue(11), IndValue(12)
If EOF(FileNo) And IndValue(9) = 0 Then
GETLATESTRPIY = IndicesDate - 1
Close #FileNo
Exit Function
ElseIf EOF(FileNo) And IndValue(9) > 0 Then
GETLATESTRPIY = IndicesDate
Close #FileNo
Exit Function
ElseIf Year(DateToLookFor) = IndicesDate And IndValue(9) = 0 Then
GETLATESTRPIY = IndicesDate - 1
Close #FileNo
Exit Function
ElseIf Year(DateToLookFor) = IndicesDate And IndValue(9) > 0 Then
GETLATESTRPIY = IndicesDate
Close #FileNo
Exit Function
End If
Loop Until EOF(FileNo)
Close #FileNo
End FunctionThe data file looks like this:
1947, 0.0, 0.0, 0.0, 0.0, 0.0, 7.3, 7.4, 7.3, 7.4, 7.4, 7.6, 7.6
1948, 7.6, 7.8, 7.8, 7.9, 7.9, 8.1, 7.9, 7.9, 7.9, 7.9, 8.0, 8.0
1949, 8.0, 8.0, 8.0, 8.0, 8.1, 8.1, 8.1, 8.1, 8.2, 8.2, 8.2, 8.3
1950, 8.3, 8.3, 8.3, 8.4, 8.4, 8.4, 8.4, 8.3, 8.4, 8.4, 8.5, 8.5
1951, 8.6, 8.6, 8.7, 8.9, 9.1, 9.2, 9.2, 9.3, 9.4, 9.5, 9.5, 9.5
1952, 9.7, 9.8, 9.8, 9.9, 9.9, 10.1, 10.1, 10.0, 10.0, 10.1, 10.1, 10.1
1953, 10.2, 10.2, 10.3, 10.3, 10.3, 10.3, 10.3, 10.3, 10.3, 10.3, 10.3, 10.3
1954, 10.3, 10.3, 10.3, 10.4, 10.3, 10.4, 10.6, 10.6, 10.5, 10.6, 10.6, 10.6
1955, 10.7, 10.7, 10.7, 10.8, 10.8, 11.0, 11.0, 10.9, 11.0, 11.2, 11.3, 11.3
1956, 11.3, 11.3, 11.4, 11.6, 11.5, 11.5, 11.5, 11.5, 11.5, 11.6, 11.6, 11.6
1957, 11.7, 11.7, 11.7, 11.8, 11.8, 11.9, 12.0, 12.0, 11.9, 12.0, 12.1, 12.2
1958, 12.2, 12.1, 12.2, 12.3, 12.3, 12.4, 12.2, 12.2, 12.2, 12.3, 12.3, 12.4
1959, 12.4, 12.4, 12.4, 12.3, 12.3, 12.3, 12.3, 12.3, 12.2, 12.3, 12.4, 12.4
1960, 12.4, 12.4, 12.3, 12.4, 12.4, 12.5, 12.5, 12.4, 12.4, 12.5, 12.6, 12.6
1961, 12.6, 12.6, 12.7, 12.8, 12.8, 12.9, 12.9, 13.0, 13.0, 13.0, 13.2, 13.2
1962, 13.2, 13.2, 13.3, 13.4, 13.5, 13.6, 13.5, 13.4, 13.4, 13.4, 13.5, 13.5
1963, 13.6, 13.7, 13.7, 13.7, 13.7, 13.7, 13.7, 13.6, 13.7, 13.7, 13.7, 13.8
1964, 13.8, 13.8, 13.9, 14.0, 14.1, 14.2, 14.2, 14.2, 14.2, 14.3, 14.4, 14.4
1965, 14.5, 14.5, 14.5, 14.8, 14.9, 14.9, 14.9, 14.9, 14.9, 15.0, 15.0, 15.1
1966, 15.1, 15.1, 15.1, 15.3, 15.4, 15.5, 15.4, 15.5, 15.5, 15.5, 15.6, 15.6
1967, 15.7, 15.7, 15.7, 15.8, 15.8, 15.8, 15.7, 15.7, 15.7, 15.8, 15.9, 16.0
1968, 16.1, 16.1, 16.2, 16.5, 16.5, 16.6, 16.6, 16.6, 16.6, 16.7, 16.8, 17.0
1969, 17.1, 17.2, 17.2, 17.4, 17.4, 17.5, 17.5, 17.4, 17.5, 17.6, 17.6, 17.8
1970, 17.9, 18.0, 18.1, 18.4, 18.4, 18.5, 18.6, 18.6, 18.7, 18.9, 19.0, 19.2
1971, 19.4, 19.5, 19.7, 20.1, 20.3, 20.4, 20.5, 20.5, 20.6, 20.7, 20.8, 20.9
1972, 21.0, 21.1, 21.2, 21.4, 21.5, 21.6, 21.7, 21.9, 22.0, 22.3, 22.4, 22.5
1973, 22.6, 22.8, 22.9, 23.3, 23.5, 23.7, 23.8, 23.8, 24.0, 24.5, 24.7, 24.9
1974, 25.3, 25.8, 26.0, 26.9, 27.3, 27.6, 27.8, 27.8, 28.1, 28.7, 29.2, 29.6
1975, 30.4, 30.9, 31.5, 32.7, 34.1, 34.8, 35.1, 35.3, 35.6, 36.1, 36.6, 37.0
1976, 37.5, 38.0, 38.2, 38.9, 39.3, 39.5, 39.6, 40.2, 40.7, 41.4, 42.0, 42.6
1977, 43.7, 44.1, 44.6, 45.7, 46.1, 46.5, 46.6, 46.8, 47.1, 47.3, 47.5, 47.8
1978, 48.0, 48.3, 48.6, 49.3, 49.6, 50.0, 50.2, 50.5, 50.7, 51.0, 51.3, 51.8
1979, 52.5, 53.0, 53.4, 54.3, 54.7, 55.7, 58.1, 58.5, 59.1, 59.7, 60.3, 60.7
1980, 62.2, 63.1, 63.9, 66.1, 66.7, 67.4, 67.9, 68.1, 68.5, 68.9, 69.5, 69.9
1981, 70.3, 70.9, 72.0, 74.1, 74.6, 75.0, 75.3, 75.9, 76.3, 77.0, 77.8, 78.3
1982, 78.7, 78.8, 79.4, 81.0, 81.6, 81.9, 81.9, 81.9, 81.9, 82.3, 82.7, 82.5
1983, 82.6, 83.0, 83.1, 84.3, 84.6, 84.8, 85.3, 85.7, 86.1, 86.4, 86.7, 86.9
1984, 86.8, 87.2, 87.5, 88.6, 89.0, 89.2, 89.1, 89.9, 90.1, 90.7, 91.0, 90.9
1985, 91.2, 91.9, 92.8, 94.8, 95.2, 95.4, 95.2, 95.5, 95.4, 95.6, 95.9, 96.0
1986, 96.2, 96.6, 96.7, 97.7, 97.8, 97.8, 97.5, 97.8, 98.3, 98.5, 99.3, 99.6
1987,100.0,100.4,100.6,101.8,101.9,101.9,101.8,102.1,102.4,102.9,103.4,103.3
1988,103.3,103.7,104.1,105.8,106.2,106.6,106.7,107.9,108.4,109.5,110.0,110.3
1989,111.0,111.8,112.3,114.3,115.0,115.4,115.5,115.8,116.6,117.5,118.5,118.8
1990,119.5,120.2,121.4,125.1,126.2,126.7,126.8,128.1,129.3,130.3,130.0,129.9
1991,130.2,130.9,131.4,133.1,133.5,134.1,133.8,134.1,134.6,135.1,135.6,135.7
1992,135.6,136.3,136.7,138.8,139.3,139.3,138.8,138.9,139.4,139.9,139.7,139.2
1993,137.9,138.8,139.3,140.6,141.1,141.0,140.7,141.3,141.9,141.8,141.6,141.9
1994,141.3,142.1,142.5,144.2,144.7,144.7,144.0,144.7,145.0,145.2,145.3,146.0
1995,146.0,146.9,147.5,149.0,149.6,149.8,149.1,149.9,150.6,149.8,149.8,150.7
1996,150.2,150.9,151.5,152.6,152.9,153.0,152.4,153.1,153.8,153.8,153.9,154.4
1997,154.4,155.0,155.4,156.3,156.9,157.5,157.5,158.5,159.3,159.5,159.6,160.0
1998,159.5,160.3,160.8,162.6,163.5,163.4,163.0,163.7,164.4,164.5,164.4,164.4
1999,163.4,163.7,164.1,165.2,165.6,165.6,165.1,165.5,166.2,166.5,166.7,167.3
2000,166.6,167.5,168.4,170.1,170.7,171.1,170.5,170.5,171.7,171.6,172.1,172.2
2001,171.1,172.0,172.2,173.1,174.2,174.4,173.3,174.0,174.6,174.3,173.6,173.4
2002,173.3,173.8,174.5,175.7,176.2,176.2,175.9,176.4,177.6,177.9,178.2,178.5
2003,178.4,179.3,179.9,181.2,181.5,181.3,181.3,181.6,182.5,182.6,182.7,183.5
2004,183.1,183.8,184.6,185.7
If you require any more info, then please ask.
Regards
Martin
