☰

如何正确格式化 Excel 的 VBA 脚本中生成的组合？

tag-icon • tag-icon microsoft-excel vba microsoft-excel-2007
如何正确格式化 Excel 的 VBA 脚本中生成的组合？

因此，我在 Excel 中使用此 VBA 脚本来从 4 个独立行的数据中获取所有可能的组合。它运行良好，但是我试图弄清楚如何让它在生成使用少于所有 4 列的数据时不在末尾使用 /：

Option Explicit

Sub Sample()
    Dim i As Long, j As Long, k As Long, l As Long
    Dim CountComb As Long, lastrow As Long

    Range("G2").Value = Now

    Application.ScreenUpdating = False

    CountComb = 0: lastrow = 6

    For i = 1 To 4: For j = 1 To 4
    For k = 1 To 8: For l = 1 To 12
        Range("G" & lastrow).Value = "key=" & Range("A" & i).Value & "&details=" & _
                                     Range("B" & j).Value & "/" & _
                                     Range("C" & k).Value & "/" & _
                                     Range("D" & l).Value
        lastrow = lastrow + 1
        CountComb = CountComb + 1
    Next: Next
    Next: Next

    Range("G1").Value = CountComb
    Range("G3").Value = Now

    Application.ScreenUpdating = True
End Sub

对于不使用全部 4 列的，它们将像这样生成：

key=1&details=1/
key=1&details=1/2/   
key=1&details=1/2/3/
key=1&details=1/2/3/4

当我希望它生成为：

key=1&details=1
key=1&details=1/2
key=1&details=1/2/3
key=1&details=1/2/3/4

我确信有一个简单的解决方案，但我似乎想不出来。有什么想法吗？

答案1

在行前添加以下代码lastrow = lastrow + 1

        Dim myvalue As String
        myvalue = Range("G" & lastrow).Value
        
        While Right(myvalue, 1) = "/"
            'Remove last /
            Range("G" & lastrow).Value = Left(myvalue, Len(myvalue) - 1)
            myvalue = Range("G" & lastrow).Value
        Wend

您的货品如下：

Option Explicit

Sub Sample()
    Dim i As Long, j As Long, k As Long, l As Long
    Dim CountComb As Long, lastrow As Long

    Range("G2").Value = Now

    Application.ScreenUpdating = False

    CountComb = 0: lastrow = 6

    For i = 1 To 4: For j = 1 To 4
    For k = 1 To 8: For l = 1 To 12
        Range("G" & lastrow).Value = "key=" & Range("A" & i).Value & "&details=" & _
                                     Range("B" & j).Value & "/" & _
                                     Range("C" & k).Value & "/" & _
                                     Range("D" & l).Value
                                     
        Dim myvalue As String
        myvalue = Range("G" & lastrow).Value
        
        While Right(myvalue, 1) = "/"
            'Remove last /
            Range("G" & lastrow).Value = Left(myvalue, Len(myvalue) - 1)
            myvalue = Range("G" & lastrow).Value
        Wend
        
        lastrow = lastrow + 1
        CountComb = CountComb + 1
    Next: Next
    Next: Next

    Range("G1").Value = CountComb
    Range("G3").Value = Now

    Application.ScreenUpdating = True
End Sub

相关内容