avi brought next idea :
Hello,
I want to recode values in a MainRange according to rules found in an
IndexTable with 3 columns : the 2 first columns delimit a range and
the 3rd one is the value to assign to the MainRange values if they
lay in that range
What would be the most efficient approach (some array function I
guess)
Thanks
Avi
If this is the same Q you asked in '...
vb.general.discussion' then...
A1: 3,5,7
A2: 2,4,6
B1: 1; C1: 4; D1: 57
B2: 5; C2: 7; D2: 88
Option Explicit
Sub RecodeValues()
Dim vValsToRecode, vSourceArray, vTemp
Dim i As Long, j As Long, k As Long
vValsToRecode = Range("A1:A2")
vSourceArray = Range("B1:D2")
For i = LBound(vValsToRecode) To UBound(vValsToRecode)
vTemp = Split(vValsToRecode(i, 1), ",")
For j = LBound(vSourceArray) To UBound(vSourceArray)
For k = LBound(vTemp) To UBound(vTemp)
Select Case CLng(vTemp(k))
Case vSourceArray(j, 1) To vSourceArray(j, 2)
vTemp(k) = vSourceArray(j, 3)
End Select 'Case CLng(vTemp(k))
Next 'k
Next 'j
vValsToRecode(i, 1) = Join(vTemp, ",")
Next 'i
Range("A1").Resize(UBound(vValsToRecode)) = vValsToRecode
End Sub
Results:
A1: 57,88,88
A2: 57,57,88
--
Garry
Free usenet access at
http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc