excel - 尝试根据上面的数字用字母字符填充空白

Question

我有这个。

3
3.1
3.2
3.3
3.4
4
4.1
NULL
NULL
NULL
NULL
5

我想要这个。

3
3.1
3.2
3.3
3.4
4
4.1
4.A
4.B
4.C
4.D
5

我大约有 22k 行需要填写。我该怎么做？我将下面的代码放在一起，但它没有做我想要的。每当发现新的空白单元格时，我都需要从先前的数字+“A”开始。

这是我目前所拥有的，但它不起作用。

Sub AlphaFill()
    Dim Cell, CellChars
    Dim Default, Prompt, Title
    Dim rangeSelected As Range
    Dim UpperCase As Boolean
    On Error Resume Next
    Set rangeSelected = Range("F1:F21400")

    For Each Cell In rangeSelected

        If Cell.Value <> "" Then
            i = 1
        End If

        If Cell.Value = "" Then
            CellChars = Chr(64 + i)
            If Not UpperCase Then CellChars = UCase(CellChars)
            Cell.Value = Cell.Value & CellChars
            i = i + 1
        End If

        Debug.Print Cell.Value

    Next

End Sub

问题是，我似乎无法保留先前的单元格，例如 4.A、4.B、4.C 和 4.D

Answer 1

如果我理解正确，那么您需要存储您看到的最后一个整数，然后在下一个空白单元格上使用它，重置间隙之间的计数器:

Sub AlphaFill()
    Dim Cell            As Range
    Dim UpperCase       As Boolean
    Dim LastWholeNumber As String
    Dim LastLetter      As Long
    Dim CurrentNumber   As String

    UpperCase = True

    For Each Cell In Range("F1:F21400")
        CurrentNumber = Cell.Value

        If CurrentNumber = "" Then
            LastLetter = LastLetter + 1
            Cell.Value = LastWholeNumber & "." & ChrW$(LastLetter)
        ElseIf InStr(CurrentNumber, ".") = 0 Then
            '// whole number - store it & reset to A/a 
            LastLetter = IIf(UpperCase, 64, 97)
            LastWholeNumber = CurrentNumber
        End If
    Next
End Sub