Like many others, I am an absolute newbie to Excel VBA but am a
relative "oldbie" at Excel - having used it for many years preceeded
by Lotus 123, preceeded by PlanCalc, MultiPlan and several nameless
others.
Anyway, I have a problem at work...... I need to match daily prices
between two different pricing systems. These are listed sequentially
in two columns with appropriate dates.
The most likely match will be today's date (ie on the same row) the
next most likely will be the preceeding row and, after that, the
immediatley following row. If they fail to match, I need to try the
row before and the row after those - expanding forwards and backwards
(upwards and downwards) from the current row.
I have to do this for tens of thousands of prices!!
The built-in MATCH function sort of does this except that it will only
work in one direction and looks through the range for the first match.
It stops there and gives a result,even if there is a better match
'later' ie further on in the range. I thus get numerous spurious matches.
I have written (my first ever) UDF to do this and have included the
code below. I have two problems with the code:
1 It runs astonishingly slowly.
2 There HAS to be a more elegant way of initialising a string than
I have used.
So, here goes - I now expose to public scrutiny and humiliation, the
best I have managaed to do!
Public Function mat()
Dim I As Variant
Dim Count As Integer
Dim rLamda, rICON As Range
Const K = 24
Dim RowOffset(K) As Integer
Dim Match As Boolean
RowOffset(0) = 0
RowOffset(1) = -1
RowOffset(2) = 1
RowOffset(3) = -2
RowOffset(4) = 2
RowOffset(5) = -3
RowOffset(6) = 3
RowOffset(7) = -4
RowOffset(8) = 4
RowOffset(9) = -5
RowOffset(10) = 5
RowOffset(11) = -6
RowOffset(12) = 6
RowOffset(13) = -7
RowOffset(14) = 7
RowOffset(15) = -8
RowOffset(16) = -9
RowOffset(17) = -10
RowOffset(18) = -11
RowOffset(19) = -12
RowOffset(20) = -13
RowOffset(21) = -14
RowOffset(22) = -15
RowOffset(23) = -16
RowOffset(24) = -17
Set rLamda = ActiveCell.Offset(0, -3)
For Each I In RowOffset
Set rICON = ActiveCell.Offset(I, -1)
If rLamda.Value = rICON.Value Then
mat = I
Match = True
Exit For
Else
mat = 99
Match = False
End If
Next I
End Function