将这个子输入作为一个单元格更改为一个单元格范围

Question

我想修改此子代码，以代替一次接受一个单元格，一个从一个源到另一个目标，接受一系列单元格，源范围和目标范围。

我的目标是调用这个Sub从一个输入范围的单元格中复制数据验证并通过调用这个Sub将它们粘贴到另一个单元格区域。这个Sub是为了能够被其他Subs（也许是函数）调用，所以它需要是可重用的。任何人都可以为我解释这个问题吗？

我想修改的代码是：

Sub CopyValidation(ByRef rngSourceCell As Range, ByRef rngTargetCell As Range) 
    With rngTargetCell.Validation 
     .Delete 
     .Add Type:=rngSourceCell.Validation.Type, _ 
      AlertStyle:=rngSourceCell.Validation.AlertStyle, _ 
      Operator:=rngSourceCell.Validation.Operator, Formula1:=rngSourceCell.Validation.Formula1, Formula2:=rngSourceCell.Validation.Formula2 
     .ErrorMessage = rngSourceCell.Validation.ErrorMessage 
     .ErrorTitle = rngSourceCell.Validation.ErrorTitle 
     .IgnoreBlank = rngSourceCell.Validation.IgnoreBlank 
     .IMEMode = rngSourceCell.Validation.IMEMode 
     .InCellDropdown = rngSourceCell.Validation.InCellDropdown 
     .InputMessage = rngSourceCell.Validation.InputMessage 
     .InputTitle = rngSourceCell.Validation.InputTitle 
     .ShowError = rngSourceCell.Validation.ShowError 
     .ShowInput = rngSourceCell.Validation.ShowInput 
    End With 
End Sub 

Answer 1

你并不需要设置每个参数分别。试想一下：

Sub dural() 
    Dim r1 As Range, r2 As Range 

    Set r1 = Range("A1:A10") 
    Set r2 = Range("B1:B10") 
    r1.Copy 
    r2.PasteSpecial xlPasteValidation 
End Sub 

即使你的单细胞程序可以使用此：

Sub CopyValidation(ByRef rngSourceCell As Range, ByRef rngTargetCell As Range) 
    rngSourceCell.Copy 
    rngTargetCell.PasteSpecial xlPasteValidation 
End Sub 

事实上，第二个程序可以同时处理单细胞和多细胞范围（只要范围是同构）