Excel用宏做循环

Question

Sub 宏1()
'
' 宏1 宏
'
' 快捷键: Ctrl+a
'
Range("C1229").Select
Selection.AutoFill Destination:=Range("C1228:C1229"), Type:=xlFillDefault
Range("C1228:C1229").Select
Range("AK1228:AM1228").Select
Selection.Copy
Range("AQ1228").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
上面是我在EXCEL中录制的一个宏

现在要求做循环，比如下一个应该是这样：
Range("C1228").Select
Selection.AutoFill Destination:=Range("C1227:C1228"), Type:=xlFillDefault
Range("C1227:C1228").Select
Range("AK1227:AM1227").Select
Selection.Copy
Range("AQ1227").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

也就是说，括号里所有的数值减去1，直到("C20")、("C19:C20")、("AK19:AM19")、("AQ19")为止，请各位高手帮助，谢谢！

Answer 1

Sub 宏1()
Dim r&
For r = 1228 To 19 Step -1
With Range("C" & r + 1)
.AutoFill .Cells(0, 1).Resize(2), xlFillDefault
End With
Range("AK" & r & ":AM" & r).Copy
Range("AQ" & r).PasteSpecial xlPasteValues, xlNone, False, False
Next
Application.CutCopyMode = False
End Sub本回答被提问者采纳

Answer 2

dim i
for i=1229 to 2 step -1
range("C" & i).select
Selection.AutoFill Destination:=Range("C" & i-1 & ":C" & i), Type:=xlFillDefault
Range("C" & i-1 & ":C" & i).Select
Range("AK" & i-1 &":AM" & i-1").Select
Selection.Copy
Range("AQ" & i-1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
next

以上只是根据你的代码修改，只是为了循环，不考虑你原代码的准确性

Answer 3

Sub 宏名称()
Dim x%
For x = 2 To 30 Step 1
If Sheet1.Cells(x, 1) <> "" Then
Sheet2.Cells(x, 1) = Sheet1.Cells(x, 1)
Sheet2.Cells(x, 2) = Sheet1.Cells(x, 3)
End If
Next
Dim y%
For y = 2 To 30 Step 1
If Sheet2.Cells(y, 1) = "" Then
Sheet2.Rows(y).Delete
End If
Next
End Sub

Answer 4

x=19
for y=1229 to x
Range("C"&y).Select
Selection.AutoFill Destination:=Range("C"&y-1&":C"&y), Type:=xlFillDefault
Range("C"&y-1&":C"&y).Select
Range("AK"&y-1&":AM"&y-1).Select
Selection.Copy
Range("AQ"&y-1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
next

Answer 5

你直接说意思就行了，你录制的宏比较麻烦