Recode values

[avi]

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

 

[GS]

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("B12")
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

 

[avi]

avi brought next idea :





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("B12")
  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 athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Thanks Garry,

Is this array approach supposed to be faster than looping directly on
the cells address directly?

Avi

 

[GS]

avi formulated on Wednesday :

Thanks Garry,

Is this array approach supposed to be faster than looping directly on
the cells address directly?

Avi

You're welcome!

Looping the worksheet will always be slower whether you're reading or
writing. You can time this so you see the difference. It will be
substantial if the range is large.