这是另一种无需循环即可实现您想要的效果的方法。我已经对代码进行了注释，因此您理解它应该不会有问题。

基本逻辑

要得到空格后的部分，可以使用公式=IFERROR(MID(G2,SEARCH(" ",G2,1),LEN(G2)-SEARCH(" ",1)+1),"")

现在在整个范围内应用公式并使用 INDEX(FORMULA) 获取值。您可以在 Convert an entire range to uppercase without looping through all the cells

代码

Option Explicit

Sub Sample()
    Dim ws As Worksheet
    Dim rng As Range
    Dim lRow As Long,i As Long
    Dim FinalAr As Variant
    
    '~~> Set this to the relevant sheet
    Set ws = Sheet1
    
    With ws
        '~~> Find last row in col G
        lRow = .Range("G" & .Rows.Count).End(xlUp).Row
        
        '~~> Set your range
        Set rng = .Range("G2:G" & lRow)
        
        '~~> Get all the last names from the range and store them
        '~~> in an array in 1 go!
        FinalAr = Evaluate("index(IFERROR(MID(" & _
                           rng.Address & _
                           ",SEARCH("" ""," & _
                           rng.Address & _
                           ",LEN(" & _
                           rng.Address & _
                           ")-SEARCH("" "",""""),)")
    End With
    
    '~~> Check the output
    For i = LBound(FinalAr) To UBound(FinalAr)
        Debug.Print ">"; FinalAr(i,1)
    Next i
End Sub

在行动

替代方法

1. 使用 Text To 列，然后将输出存储在数组中
2. 使用快速填充获取姓氏，然后将输出存储在数组中。这种方法的一个缺点是没有姓氏的名字，它会显示名字而不是空白。

     <div class="card">
        <form id="inputForm">
            <label>Income</label><br>
              <input type="text" id="incMoney">
              <br>
              <br>
              <button id="submit">
                Calculate 
              </button>
              <br>
              <button class="reset"><i class="fas fa-undo"></i></button>
            </form>
            <div class="results">
                <p id="outcome">
                   Your income is <span id="original-input"></span>. <br>
                   <span class="fifty-title">Necesserty:</span> <span id="fiftyTxt"></span>
                   <br>
                   <span class="thirty-title">Wants:</span> <span id="thirtyTxt"></span>
                   <br>
                   <span class="twenty-title">Savings:</span> <span id="twentyTxt"></span>
                </p>
            </div>

使用 Filter() 的解决方案（排除缺少姓氏的值）：

Sub ExtractLastNames()
    Dim arr,name_array,i
    
    arr = WorksheetFunction.Transpose(Range("G2:G" & Cells(Rows.Count,"G").End(xlUp).Row)) 'first,get the horizontal one-dimentional array from cells
    name_array = Filter(arr," ",True) 'second,filter out one-word and empty elements
    For i = LBound(name_array) To UBound(name_array)
        name_array(i) = Split(name_array(i))(1) 'third,replace name_array values with extracted lastnames
    Next
    Range("H2").Resize(UBound(name_array) + 1) = WorksheetFunction.Transpose(name_array) ' output
End Sub

要数组的姓氏

• 以下将把最后出现的空格之后的子字符串视为姓氏。

Option Explicit

Sub create_namear()
    
    Dim ws As Worksheet: Set ws = ActiveSheet
    
    Dim nRange As Range
    Set nRange = ws.Range("G2:G" & ws.Range("G" & ws.Rows.Count).End(xlUp).Row)
    Dim rCount As Long: rCount = nRange.Rows.Count
    Dim nArray() As String: ReDim nArray(0 To rCount - 1)
    
    Dim nCell As Range
    Dim n As Long
    Dim nmLen As Long
    Dim LastSpacePosition As Long
    Dim nmString As String
    Dim LastName As String
    
    For Each nCell In nRange.Cells
        nmString = CStr(nCell.Value)
        If InStr(1,nmString," ") > 0 Then
            LastSpacePosition = InStrRev(nCell.Value," ")
            nmLen = Len(nmString)
            If LastSpacePosition < nmLen Then
                LastName = Right(nmString,nmLen - LastSpacePosition)
                nArray(n) = LastName
                n = n + 1
            End If
        End If
    Next nCell
    
    If n = 0 Then Exit Sub
    If n < rCount Then
        ReDim Preserve nArray(0 To n - 1)
    End If
    
    Debug.Print "[" & LBound(nArray) & "," & UBound(nArray) & "]" _
        & vbLf & Join(nArray,vbLf)

End Sub

悉达多公式评价的扩展

这些对 Siddharth 的有效代码的补充可能会有所帮助，如果数据行少于 2 行以避免

• 对标题行 1:1 的不需要的评估（如果根本没有数据，请参阅第 1.b 部分）- 这可以通过以下方式防止将仅 lRow 的结果行号 1 更正为 2 的实际数据行开头。
• 错误 9 下标超出范围（如果是单个元素；请参阅第 3.b 节） - 请注意这需要通过适当维数的 tmp 数组将一维结果转换为二维结果数组。

此外，我简化了公式构建以避免重复 rng.Address 插入只是为了展示另一种方法（请参阅第 {{1} })。

2.