VBA - Assign a Code at Level Changes Based on Criteria

xtiinctt

New Member
Joined
Aug 5, 2024
Messages
4
Office Version
  1. 365
  2. 2013
Platform
  1. Windows
Hello all!

Hoping that someone could help me or even tell me if this is possible! First time poster but have been looking at posts in the forum for a long time!

Column B - I have levels that have a code assigned to them based on column E, and column G are my expected results.
If the level changes and has a code in column E then the proceeding level values will be that value, but if the level goes down to 3 in this case, we want the code from when the value was a 3 or if a new code is assigned in like in column E18 use that until the level goes back down.

Columns C, D, and F don't play a part in this scenario.

I was looking at recursive solutions but that is something I have never used, so out of my wheelhouse!

thank you all for your time!
 

Attachments

  • Screenshot 2024-08-05 165307.png
    Screenshot 2024-08-05 165307.png
    51.5 KB · Views: 10

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
Welcome to the MrExcel forum!

As far as I can tell, no recursion needed. Try:

VBA Code:
Sub AssignCode()
Dim dic As Object, Level As Variant, Code As Variant, Expected As Variant
Dim i As Long, LastLevel As Long

    Level = Range("B2", Range("B2").End(xlDown)).Value
    Code = Range("E2").Resize(UBound(Level)).Value
    Expected = Range("G2").Resize(UBound(Level)).Value
    
    Set dic = CreateObject("Scripting.Dictionary")
    LastLevel = -1
    For i = 1 To UBound(Level)
        If Level(i, 1) <> LastLevel Then
            If Code(i, 1) <> "" Then
                dic(Level(i, 1)) = Code(i, 1)
            Else
                If Not dic.exists(Level(i, 1)) Then dic(Level(i, 1)) = Expected(i - 1, 1)
            End If
        End If
        Expected(i, 1) = dic(Level(i, 1))
        LastLevel = Level(i, 1)
    Next i
    
    Range("G2").Resize(UBound(Level)) = Expected
                    
End Sub

This code creates the expected results, except for the last line (index 42). You have the expected value as c, but the level did not change, so the macro did not change the code. Do you want the code to change if the code in column E changes, even if the level doesn't?
 
Upvote 0
Welcome to the MrExcel forum!

As far as I can tell, no recursion needed. Try:

VBA Code:
Sub AssignCode()
Dim dic As Object, Level As Variant, Code As Variant, Expected As Variant
Dim i As Long, LastLevel As Long

    Level = Range("B2", Range("B2").End(xlDown)).Value
    Code = Range("E2").Resize(UBound(Level)).Value
    Expected = Range("G2").Resize(UBound(Level)).Value
   
    Set dic = CreateObject("Scripting.Dictionary")
    LastLevel = -1
    For i = 1 To UBound(Level)
        If Level(i, 1) <> LastLevel Then
            If Code(i, 1) <> "" Then
                dic(Level(i, 1)) = Code(i, 1)
            Else
                If Not dic.exists(Level(i, 1)) Then dic(Level(i, 1)) = Expected(i - 1, 1)
            End If
        End If
        Expected(i, 1) = dic(Level(i, 1))
        LastLevel = Level(i, 1)
    Next i
   
    Range("G2").Resize(UBound(Level)) = Expected
                   
End Sub

This code creates the expected results, except for the last line (index 42). You have the expected value as c, but the level did not change, so the macro did not change the code. Do you want the code to change if the code in column E changes, even if the level doesn't?
Hey Eric!

Thank you for spending time reviewing my post! that is correct, I want the code to change even if the level doesn't because there could be an instance where the code is "e" for 5 rows on level 4, but then change to "f" on the same level and I would expect "f" to be the expected results. I will test this out tomorrow morning against my data set and let you know how it goes!
 
Upvote 0
Hey Eric!

Thank you for spending time reviewing my post! that is correct, I want the code to change even if the level doesn't because there could be an instance where the code is "e" for 5 rows on level 4, but then change to "f" on the same level and I would expect "f" to be the expected results. I will test this out tomorrow morning against my data set and let you know how it goes!
Sorry for the additional response but tested the logic out, and the "expected" column is what I would expect the VBA to return and is not a part of the original dataset. Sorry I did not make that clear in my original post. Please let me know if I can provide any additional data :)
 
Upvote 0
Try this version:

Rich (BB code):
Sub AssignCode()
Dim dic As Object, Level As Variant, Code As Variant, Expected As Variant
Dim i As Long, LastLevel As Long

    Level = Range("B2", Range("B2").End(xlDown)).Value
    Code = Range("E2").Resize(UBound(Level)).Value
    Expected = Range("G2").Resize(UBound(Level)).Value
    
    Set dic = CreateObject("Scripting.Dictionary")
    LastLevel = -1
    For i = 1 To UBound(Level)
        If Code(i, 1) <> "" Then dic(Level(i, 1)) = Code(i, 1)
        If Level(i, 1) <> LastLevel Then
            If Not dic.exists(Level(i, 1)) Then dic(Level(i, 1)) = Expected(i - 1, 1)
        End If
        Expected(i, 1) = dic(Level(i, 1))
        LastLevel = Level(i, 1)
    Next i
    
    Range("G2").Resize(UBound(Level)) = Expected
                    
End Sub

Make sure to change the cell references to match your sheet.
 
Upvote 0
Hey Eric,

I was able to create some more data to test against and added some additional comments for clarity. First thing, I get a error at line "Then dic(Level(i, 1)) = Expected(i - 1, 1)" - Subscript out of range. I did adjust my ranges, but the "expected" column is what I want the macro to return and is not a part of the original data.

Here are some additional notes that might be helpful:

The code gets applied from the top down, which I think you are super close! I think there is another if statement possibly needed? either way, so if level 5 has a code, only the level 6 or lower would get that code.
Meaning..
level 4 - code "E" gets applied down unless it runs into another code on level 5 say "F" then that gets applied down.
if no code on level 5 or additional levels inherit level 4

hopefully this clears up any confusion, and again thank you for your assistance!





Book1.xlsx
ABC
1IndexLevelCode
210
321
431
542a
652a
762a
870b
981
1091
11101
12111
13121
14131
15141
16151
17161
18171
19181
20191
21201
22211
23221
24231
25241
26251
27260b
28271
29281
30291
31301d
32311
33321
34331
35340
36351
37361
38371
39381
40391
41401e
42412
43423a
44433a
45443a
46453
47463
48473
49483
50493
51503
52513
53523
54533
55543
56553
57563
58573
59583
60593
61603
62613
63623
64633
65643
66653
67663
68673
69683
70693
71703
72713
73723
74733
75743a
76753a
77763a
78773a
79783a
80793
81803
82813
83823
84833
85842
86852
87863
88873
89883
90893
91902
92912
93922
94932
95942
96951
97962
98972
99982
100993
1011003
1021013
1031023
1041033
1051043
1061053
1071063
1081073
1091083
1101093
1111103
1121113
1131123
1141133
1151143
1161153
1171163
1181173
1191183
1201193
1211203
1221213
1231223
1241233
1251243
1261252
1271262
1281273
1291282
1301292
1311302
1321312
1331322
1341332
1351341f
1361352
1371362
1381372
1391382
1401392
1411402
1421412
1431422
1441432f
1451443
1461453
1471463
1481474b
1491484
1501494
1511504
1521514
1531524
1541534
1551544
1561554
1571564
1581574
1591584
1601594
1611604
1621614
1631624
1641634
1651644
1661653
1671664
1681674
1691684
1701694
1711704
1721714
1731724
1741734
1751744
1761753
1771764
1781774
1791784
1801794
1811804
1821814
1831824
1841834
1851844
1861853f
1871864a
1881874
1891884
1901894
1911904
1921914
1931924
1941934
1951944
1961954
1971964
1981974
1991984
2001994g
2012004
2022014
2032024
2042034
2052044h
2062055a
2072065a
2082075a
2092085
2102095
2112105
2122116
2132125
2142135a
2152145
2162156
2172166
2182177
2192187
2202197
2212208
2222217
2232227
2242237
2252247
2262257
2272267
2282277
2292287
2302296
2312306
2322316
2332326
2342336
2352346
2362355
2372365
2382375
2392385a
2402395
2412405
2422415
2432425
2442435
2452445
2462455
2472465
2482475
2492485
2502495
2512505
2522515
2532525
2542534i
2552544f
2562554
2572564
2582574
2592584
2602594
2612604
2622614
2632624
2642634
2652644
2662654
2672664
2682674
2692684
2702694
2712704
2722714
2732724
2742734
2752744
2762754
2772764
2782774
2792784
2802794
2812804
2822814
2832824
2842834
2852844
2862854
2872863f
2882874
2892884
2902894
2912904
2922914
2932924
2942934
2952944
2962954
2972964
2982974
2992984
3002994
3013004
3023014
3033024
3043034
3053044
3063054
3073064
3083075i
3093084
3103094
3113104
3123114g
3133125
3143135
3153145
3163155
3173165
3183176
3193187
3203198
3213207
3223216
3233227
3243235
3253245j
3263255
3273265
3283275
3293285
3303295
3313304
3323314
3333324
3343334
3353344
3363354
3373364
3383373f
3393384
3403394
3413404
3423414
3433424
3443434
3453444
3463454
3473464
3483474
3493484
3503494
3513504
3523513k
3533524
3543534
3553544
3563554
3573564
3583574
3593584
3603594
3613604
3623614
3633624
3643634l
3653645a
3663655a
3673665a
3683675
3693685
3703695
3713705
3723715
3733725
3743736
3753746
3763756
3773766
3783776
3793786
3803796
3813806
3823816a
3833826
3843836
3853846
3863856
3873866
3883876
3893887
3903897
3913907
3923918
3933928
3943938
3953948
3963958
3973968
3983978
3993987
4003997
4014007
4024017
4034027
4044037
4054047
4064057
4074067
4084077
4094087
4104097
4114107
4124117
4134127
4144137
4154147
4164157
4174167
4184177
4194187
4204197
4214207
4224217
4234227
4244237
4254247
4264257
4274266
4284276
4294286
4304296
4314306
4324316
4334326
4344336
4354346
4364356
4374366
4384376
4394386
4404396
4414406
4424416
4434426
4444436
4454446
4464456
4474466
4484476
4494486
4504496
4514506
4524516
4534526
4544536
4554546
4564556
4574566
4584576
4594586
4604596
4614606
4624616
4634626
4644636
4654646
4664656
4674666
4684676
4694686
4704696
4714706
4724716
4734726
4744736
4754746
4764756
4774766
4784776
4794786
4804796
4814806
4824816
4834826
4844835
4854845a
4864855
4874865
4884875
4894885
4904895
4914905
4924915
4934925
4944935
4954945
4964955
4974965
4984975
4994985
5004994i
5015004f
5025014
5035024
5045034
5055044
5065054m
5075065
5085074
5095084
5105094
5115104n
5125115
5135126
5145136
5155147
5165158
5175167
5185177
5195186
5205197
5215206
5225216
5235226
5245236
5255246
5265256
5275266
5285276
5295286
5305296
5315306
5325316
5335326
5345336
5355346
5365356
5375366
5385375
5395386
5405396
5415406
5425417
5435426
5445436
5455446
5465456
5475466
5485476
5495486
5505496
5515506
5525516
5535526
5545536
5555546
5565556
5575567
5585577
5595587
5605597
5615606
5625617
5635626
5645637
5655646
5665656
5675666
5685676
5695685
5705695
5715705
5725715
5735725
5745735
5755745
5765755
5775764
5785774
5795784
5805794
5815804
5825814
5835824
5845834
5855844
5865854
5875864
5885874
5895884
5905894
5915904
5925914
5935923g
5945934
5955944
5965954
5975964
5985974
5995984
6005994
6016004
6026014
6036024
6046034
6056044
6066054
6076064
6086074
6096084
6106094
6116104
6126114
6136124
6146134
6156144
6166154
6176164
6186175
6196185
6206195
6216205
6226215
6236224
6246234
6256244
6266254
6276265
6286275
6296285
6306295
6316305
6326315
6336325
6346335
6356345
6366355
6376364
6386375a
6396385a
6406395a
6416404
6426415
6436425
6446435
6456445
6466455
6476465
6486476
6496484g
6506495
6516505
6526516
6536527
6546536
6556547
6566555
6576565
6586575
6596584
6606594
6616604
6626614
6636624
6646634
6656644
6666654
6676665a
6686675a
6696685
6706696
6716706
6726716
6736726
6746737
6756747
6766757
6776766
6786776
6796786
6806796
6816807
6826816
6836827
6846835
6856846a
6866856a
6876866a
6886876a
6896886a
6906896a
6916906a
6926916a
6936926a
6946936a
6956946a
6966956a
6976966a
6986976a
6996986a
7006996a
7017006a
7027016a
7037026a
7047036a
7057047a
7067056
7077066
7087076
7097087a
7107097a
7117108a
7127116
7137127
7147138
7157149
71671510a
71771610a
71871711a
71971810a
7207199
72172010a
72272110a
7237228
7247239
72572410a
7267259
72772610a
72872710a
72972811a
73072910a
7317306a
7327317a
7337326a
7347336
7357346
7367356a
7377366a
7387376a
7397386a
7407396a
7417406a
Sheet1
 
Upvote 0
OK, some clarity, but I have some questions. The error line you got was because you don't have a code in C2 in your example. My macro tries to look up the code, but if it doesn't have a code for that level, it tries to take it from the previous line. So if there is nothing on the first row, you'll get an error. So what do you want in the Results column if there's nothing on the first row, and there's nothing to inherit?

Also, your explanation of how higher levels inherit the code of lower levels makes me rethink my approach. For example, for Index 6, code a is assigned to level 2. Down to index 40, code e is assigned to level 1. The next row is level 2. Should the result be a because it was assigned to level 2, or should it be e because it inherits the e from level 1?
 
Upvote 0

Forum statistics

Threads
1,224,820
Messages
6,181,155
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top