Recode values
 

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

Recode values
 

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

Recode values
 

On 4 août, 22:09, GS wrote:
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 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

Recode values
 

avi formulated on Wednesday :
On 4 août, 22:09, GS wrote:
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 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

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.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc