Excel 公式转换成 Excel VBA code

问题遇到的现象和发生背景

问题：将Excel 公式转换成VBA里的code

为了提取出图片中B列的内容，我使用的公式是问题里描述的相关代码。
我想在VBA里面实现有A列，直接根据VBA里的编程产出B列对应的内容。不要再使用Vlookup啦。

感谢帮助～

注：操作的对象不止是AD2一个单元格，而是某列内所有行的数据。行数不确定，所以希望可以用 do while或者for去循环数据所在列里面的所有行。

数据类型：不定长的字符串 i.e. A16CFF4P42TX11 FF4T 等数据

代码想要达成的效果：根据数据，返回字母对应的详细信息。并将对应出来的详细信息放入新的一列。如果数据里只有A，那么对应内容就是 Push button 如果数据里有A，C，D，Z，则对应的内容应是 Push Button,Selector Switch,Pressure Switch,Laser (这些信息应放在同一单元格内,并且信息之间应用逗号连接)。若行是空值，则对应的信息应是none

问题相关代码，请勿粘贴截图

=(IFERROR(IF(FIND("A",AD2)>0,"Push Button, ",),""))&(IFERROR(IF(FIND("B",AD2)>0,"Bi-Metal OLR, ",),""))&(IFERROR(IF(FIND("C",AD2)>0,"Selector Switch, ",),""))&(IFERROR(IF(FIND("D",AD2)>0,"Pressure Switch, ",),""))&(IFERROR(IF(FIND("F",AD2)>0,"Control Fusing, ",),""))&(IFERROR(IF(FIND("G",AD2)>0,"Enclosure/Metering modification, ",),""))&(IFERROR(IF(FIND("K",AD2)>0,"Timer Option, ",),""))&(IFERROR(IF(FIND("L",AD2)>0,"Enclosure Size modification, ",),""))&(IFERROR(IF(FIND("M",AD2)>0,"Marine, ",),""))&(IFERROR(IF(FIND("N",AD2)>0,"Neutral Option, ",),""))&(IFERROR(IF(FIND("P",AD2)>0,"Pilot Light, ",),""))&(IFERROR(IF(FIND("Q",AD2)>0,"Bulk Pack, ",),""))&(IFERROR(IF(FIND("R",AD2)>0,"Relay Option, ",),""))&(IFERROR(IF(FIND("X",AD2)>0,"Aux Contact, ",),""))&(IFERROR(IF(FIND("Y",AD2)>0,"Misc, ",),""))&(IFERROR(IF(FIND("Z",AD2)>0,"Laser, ",),""))

我想要达到的结果

A16CFF4P42TX11 对应的结果是 Push Button,Selector Switch,Control Fusing,Pilot Light,Aux Contact
FF4T 对应的结果是 Control Fusing

对应出来的结果应放在新的一列。

Function TextMatchJoin(Delimiter As String, joinRange As Range, ParamArray Rng() As Variant)
    Dim cell As Range, i As Integer, j As Integer, item As Variant, totalCount As Integer, startRow As Integer
    Dim match As Boolean
    
    totalCount = ActiveSheet.UsedRange.Rows.Count
    If totalCount > joinRange.Count Then totalCount = joinRange.Count
    startRow = joinRange.Row
    
    For i = 0 To totalCount - 1
        item = joinRange.Cells(i + 1)
        match = True
        For j = 0 To UBound(Rng) Step 2
            If Not IsMissing(Rng(j)) And j + 1 <= UBound(Rng) Then
                If TypeName(Rng(j)) = "Range" Then
                    If Rng(j).Cells(i + 1) <> Rng(j + 1) Then
                        match = False
                    End If
                End If
            End If
        Next j
        If match Then
            TextMatchJoin = TextMatchJoin & item & Delimiter
        End If
    Next i
    If Len(TextMatchJoin) > 2 Then TextMatchJoin = Left(TextMatchJoin, Len(TextMatchJoin) - Len(Delimiter))
End Function

我想要达成的效果是下图这样的

为了提取出B列的内容，我使用的公式是问题里描述的相关代码。
我想在VBA里面直接实现有A列，直接根据VBA里的编程产出B列对应的内容。不要再使用Vlookup啦。