Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
recode variable | Excel Worksheet Functions | |||
Find matching values, copy/paste values as well as values in ColA | Excel Programming | |||
simple recode operation | Excel Worksheet Functions | |||
how do I recode a likert scale | Excel Discussion (Misc queries) | |||
Recode Varialbe Values | Excel Discussion (Misc queries) |