VBA脚本开发求助：向上查找匹配值并执行动态计算

我是VBA新手，目前尝试编写一段脚本，实现以下功能：从每行提取临时值，在当前行之前的行中查找该值；若找到匹配项，则存储新临时值并执行简单计算，此过程持续到无更多匹配项为止。

现有代码片段 #

lastr = ActiveWorkbook.Sheets("My List").Range("A1:A120000").Find("*", SearchOrder:=xlByRows, searchdirection:=xlPrevious).Row

For m = 1 To lastr
Select Case ActiveWorkbook.Sheets("My List").Cells(m, 9).Value

              'store all of these values for each row. I can add conditional statements later.

              lim_1 = ActiveWorkbook.Sheets("My List").Cells(m, 17).Value
              lim_2 = ActiveWorkbook.Sheets("My List").Cells(m, 20).Value
              lim_3 = ActiveWorkbook.Sheets("My List").Cells(m, 21).Value
              
              lower_bound1 = ActiveWorkbook.Sheets("My List").Cells(m, 22).Value
              upper_bound1 = ActiveWorkbook.Sheets("My List").Cells(m, 24).Value
              
              upper_bound2 = ActiveWorkbook.Sheets("My List").Cells(m, 25).Value
              upper_bound2 = ActiveWorkbook.Sheets("My List").Cells(m, 26).Value
              
              dash_end = ActiveWorkbook.Sheets("My List").Cells(m, 6).Value
              seq_num = ActiveWorkbook.Sheets("My List").Cells(m, 2).Value
    
              target_value = ActiveWorkbook.Sheets("My List").Cells(m, 9).Value


'  ---------------------------------------------------------------------

核心需求说明 #

检查target_value是否在当前行上方的指定范围内存在；若找到匹配项，存储临时值并执行计算，同时更新目标值（例如，若原target_value为6，在D列上方找到匹配后，新目标值变为该行I列的值）。

核心逻辑代码片段 #

Set rngRangeToLookAt = ActiveSheet.Range("D1:D724")
            Set FoundCell = rngRangeToLookAt.Find(target_value, searchdirection:=xlPrevious)

                    
            If Not FoundCell Is Nothing Then
             
             'These would be the values I would be storing if I found a match during my previous row scans
              rngFirstAddress = FoundCell.Address

                   target_value = ActiveWorkbook.Sheets("My List").Cells(m, 9).Value
                   temp_dash = ActiveWorkbook.Sheets("My List").Cells(m, 6).Value
               
                   temp_lim_1 = ActiveWorkbook.Sheets("My List").Cells(m, 17).Value
                   temp_lim_2 = ActiveWorkbook.Sheets("My List").Cells(m, 20).Value
                   temp_lim_3 = ActiveWorkbook.Sheets("My List").Cells(m, 21).Value
              
                   temp_lower_bound1 = ActiveWorkbook.Sheets("My List").Cells(m, 22).Value
                   temp_upper_bound1 = ActiveWorkbook.Sheets("My List").Cells(m, 24).Value

               
              'Simple Calculation performed below. 
                
                Simple_Calc = temp_dash * dash_end
                dash_end = Simple_Calc
                ActiveWorkbook.Sheets("My List").Cells(FoundCell, 8).Value = dash_end

                Do            
                    Set FoundCell = rngRangeToLookAt.Find(target_value, searchdirection:=xlPrevious)
                Loop Until FoundCell Is Nothing Or FoundCell.Address = rngFirstAddress
                
            End If

预期实现效果 #

展示数据行之间的匹配与计算逻辑：当某行的目标值在上方行的指定列中找到匹配时，执行乘法计算并更新对应单元格内容，且会持续查找新的匹配项直到无结果为止。

内容的提问来源于stack exchange，提问作者bitter_confusion