作为我为客户做的项目的一部分,
我让用户通过指定范围字符串来配置他的"重要"范围(例如"D5-F5、A5、G5、B4"(
这告诉我将指定的范围复制到摘要页面。
以下代码用于将"重要范围"复制到摘要页面
rangeString = shtSettings.Cells(3, 2)
set importantRange = shtSource.Range(rangeString);
importantRange.Copy
shtSummary.Cells(<someLine>, 4).PasteSpecial xlPasteValues
我发现Excel按字母顺序复制单元格,而不是按照rangeString
指定例如 rangeString = 'A5, D5, B5' 被视为 'A5, B5, D5'
这对用户来说是(可以理解的(违反直觉的。
- 如何防止 excel "排序"范围?
- 任何其他建议如何让"简单"用户定义对他很重要的范围并将其(值(复制到摘要表中
谢谢
也许是这样的:
Sub Test()
Dim Rng() As String, CopyRng As Range, RngStr As String, X As Long
RngStr = "A5,D5,B5"
With ThisWorkbook.Sheets("Sheet1")
Rng = Split(RngStr, ",")
For X = LBound(Rng) To UBound(Rng)
Set CopyRng = .Range(Rng(X))
Debug.Print CopyRng.Address 'Do Something With CopyRng
Next X
End With
End Sub
我也不想让人们在单元格之间创建带有"-"
的字符串。
既然你也要求其他想法,如果你不让用户输入字符串,而是让他们实际选择怎么办?这是一个想法吗?
Sub Test()
Dim Rng() As String, PickedRng As Range, CopyRng As Range, X As Long
Set PickedRng = Application.InputBox(Prompt:="Select cells to copy", Type:=8)
With ThisWorkbook.Sheets("Sheet1")
Rng = Split(PickedRng.Address, ",")
For X = LBound(Rng) To UBound(Rng)
Set CopyRng = .Range(Rng(X))
Debug.Print CopyRng.Address 'Do Something With CopyRng
Next X
End With
End Sub
这不是防错的,但你明白了。更多信息在这里