使用连续和非连续数字的序列进行打印 [英] Print Using Sequence of Consecutive and Non-Consecutive Numbers

问题描述

我有一个需要在宏中循环显示的序列号列表.大多数序列号是连续的，但是偶尔会丢失一些.例如，我可能需要使用序列号500-510、512-513、516.

I have a list of serial numbers I need to cycle through in a macro. Most of the serial numbers are consecutive, but occasionally a few will be missing. For example, I might need to use serial numbers 500-510, 512-513, 516.

有没有办法遍历这样的列表?我真的希望不必写出每个数字，例如:500、501、502、503 ...因为有时我可以有数百个序列号.

Is there a way to loop through a list like that? I'd really prefer not to have to write out every number, ex: 500, 501, 502, 503... because sometimes I could have hundreds of serial numbers.

此外，该列表将随每次运行而变化，因此我需要能够向用户询问序列号列表，然后将该列表插入vba宏.不知道该怎么做.

Also, the list will change with every run, so I need to be able to ask the user for the list of serial numbers and then insert that list into the vba macro. Not sure how to do that.

谢谢.

推荐答案

如果它没有比您的示例字符串复杂得多，则可以引用 Range 对象，例如:

If it doesn't get much more complicated than your sample string one could refer to a Range object, e.g.:

Sub Test()

Dim str As String: str = "500-510,512-513,516"

For Each i In Range("A" & Replace(Replace(str, "-", ":A"), ",", ",A"))
    Debug.Print i.Row
Next

End Sub

很明显，这种方法存在局限性(在连接表示 Range 的字符串的长度方向上都是如此，但在工作表上未通过行表示的潜在数字上也是如此.

It may be obvious there are limitations to this approach (both length-wise on concatenating a string that represents a Range, but also on potential numbers not represented through rows on a worksheet.

也许会更坚实一点:

Sub Test()

Dim str As String: str = "500-510,512-513,516"

For Each el In Split(str, ",")
    If InStr(1, el, "-") > 0 Then
        For x = Val(el) To Val(Right(el, InStrRev(el, "-") - 1))
            Debug.Print x
        Next
    Else
        Debug.Print Val(el)
    End If
Next

End Sub

关于输入字符串验证；您可以查看 Like 运算符或更好的正则表达式.

As for your input string validation; You could look into Like operator or better, regular expressions.

这篇关于使用连续和非连续数字的序列进行打印的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！