当用户按下宏快捷键时,我正在尝试复制一组基于活动单元格的单元格。例如,他们选择了AI10 (R10C35),我希望它复制范围R10C36:R22C79,然后将值粘贴到R11C36:R23C79。要复制的范围的末尾始终为R22C79,粘贴的末尾始终为R23C79。范围的开始是唯一根据活动单元格而变化的内容。
如果我可以获得帮助来选择范围并将其复制,我就可以从那里计算出PasteRange和HolidayRange。
我确信我的If语句也可以简化,也可以接受建设性的批评,但string to range是我的主要目标,因为其余的都是按原样工作的。
因为我目前有这个,所以我得到:
Runtime Error 1004
Method 'Range' of object '_Global' failed谢谢!
Dim CurrentColumn As Integer
Dim CopyRange As String
Dim PasteRange As String
Dim HolidayRange As String
CurrentRow = ActiveCell.Row
CurrentColumn = ActiveCell.Column
If CurrentColumn <> "35" Then MsgBox ("You must select a date in column AI")
If CurrentRow < 9 Then MsgBox ("You must select a date in column AI")
If CurrentRow > 22 Then MsgBox ("You must select a date in column AI")
If CurrentColumn <> "35" Then Exit Sub
If CurrentRow < 9 Then Exit Sub
If CurrentRow > 22 Then Exit Sub
CopyRange = "R" & CurrentRow & "C" & CurrentColumn + "1" & ":R22C79"
PasteRange = "R" & CurrentRow + "1" & "C79" & ":R23C79"
Range(CopyRange).Select
Selection.Copy
Range(PasteRange).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
HolidayRange = "R" & CurrentRow & "C36:R" & CurrentRow & "C79"
Range(HolidayRange).ClearContents发布于 2019-08-14 00:08:20
我可能没有完全理解这一点,但应该给您一个如何处理它的想法。
也值得一读how to avoid select。
Sub x()
Dim CurrentColumn As Long
currentrow = ActiveCell.Row
CurrentColumn = ActiveCell.Column
If CurrentColumn <> 35 Then MsgBox ("You must select a date in column AI"):exit sub
If currentrow < 9 Then MsgBox ("You must select a date in column AI"):exit sub
If currentrow > 22 Then MsgBox ("You must select a date in column AI"):exit sub
Range(Cells(currentrow + 1, CurrentColumn + 1), Cells(23, 79)).Value = Range(Cells(currentrow, CurrentColumn + 1), Cells(22, 79)).Value
Range(Cells(currentrow, 36), Cells(currentrow, 79)).ClearContents
End Subhttps://stackoverflow.com/questions/57481043
复制相似问题