Excel VBA - 添加左公式并从 A 到 Z 排序

Question

我有一个 VBA 代码，用于以特定格式重新排列从 Sheet 1 到 Sheet 2 的列

现在，当运行 VBA 代码时，它会在 Sheet 2 的 A 至 E 列和 G 列中正确填充数据

我想在 F 列中添加一个公式，该公式应返回 B 列中的特定数据

F 列的公式：“=LEFT(RIGHT(B2,10),9)

此公式应执行至最后一个空白行

其次，我想根据 A 列按字母顺序（a 到 z）排列此 Sheet 2

我的 VBA 代码是：

Sub ModdedMap()

Dim Sh1 As Worksheet, Sh2 As Worksheet, Sh3 As Worksheet
Dim HeadersOne As Range, HeadersTwo As Range
Dim hCell As Range

With ThisWorkbook
    Set Sh1 = .Sheets("Sheet1")
    Set Sh2 = .Sheets("Sheet2")
    Set Sh3 = .Sheets("Sheet3")
End With

Set HeadersOne = Sh3.Range("A1:A" & Sh3.Range("A" & Rows.Count).End(xlUp).Row)

Application.ScreenUpdating = False

For Each hCell In HeadersOne

    SCol = GetColMatched(Sh1, hCell.Value)
    TCol = GetColMatched(Sh2, hCell.Offset(0, 1).Value)
    LRow = GetLastRowMatched(Sh1, hCell.Value)
    
    For Iter = 2 To LRow
        Sh2.Cells(Iter, TCol).Value = Sh1.Cells(Iter, SCol).Value
    Next Iter
    
Next hCell

Application.ScreenUpdating = True
End Sub

Function GetLastRowMatched(Sh As Worksheet, Header As String) As Long
    ColIndex = Application.Match(Header, Sh.Rows(1), 0)
    GetLastRowMatched = Sh.Cells(Rows.Count, ColIndex).End(xlUp).Row
End Function

Function GetColMatched(Sh As Worksheet, Header As String) As Long
    ColIndex = Application.Match(Header, Sh.Rows(1), 0)
    GetColMatched = ColIndex
End Function

感谢任何帮助