VBA:宏分配给对象，当单击操作时，它会进入代码并触发-参数数量错误或属性分配无效

Question

我的VBA宏被分配给我刚刚粘贴在工作表中的来自互联网的图像

我的宏应该这样做：

我有40列包含信息，我想在第1列和第2列、第2列和第3列之间添加一列。依此类推

当我点击我的图片时，excel进入代码，一旦进入该窗口，就会出现一个小窗口，上面写着：

参数数量错误或属性分配无效

还突出显示了主体所在的第一行，我指的是这部分：

列("B:B"(。

你能帮我解决这个问题吗？抱歉我的业余问题

Sub columns()
'
' columns Macro
'
'
columns("B:B").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
columns("D:D").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
columns("F:F").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
columns("H:H").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
columns("J:J").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
columns("L:L").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
columns("N:N").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
columns("P:P").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
columns("R:R").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
columns("T:T").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
columns("V:V").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
columns("X:X").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
columns("Z:Z").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
columns("AB:AB").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
columns("AD:AD").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
columns("AF:AF").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
columns("AH:AH").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
columns("AJ:AJ").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
columns("AL:AL").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
columns("AN:AN").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
columns("AP:AP").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
columns("AR:AR").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
columns("AT:AT").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
columns("AV:AV").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
columns("AX:AX").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
columns("AZ:AZ").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
ActiveWindow.SmallScroll ToRight:=34
columns("BB:BB").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
columns("BD:BD").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
columns("BF:BF").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
columns("BH:BH").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
columns("BJ:BJ").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
columns("BL:BL").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
columns("BN:BN").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
columns("BP:BP").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
columns("BR:BR").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
columns("BT:BT").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
columns("BV:BV").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
columns("BX:BX").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
columns("BZ:BZ").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
End Sub

Answer 1

您可以使用您提供的整个代码，并将其精简为：

Sub insertColumns()
Dim ws As Worksheet, i As Long
Set ws = ThisWorkbook.Worksheets(1)
For i = 2 To 78 Step 2
ws.Columns(i).Insert
Next
End Sub

易于阅读-(便于调试(并且不使用.Select。

将Worksheets(1)中的1替换为表索引号或工作表名称(用双引号括起来，Worksheets("Sheet1")

---

如果你愿意，你可以使这个动态：

Sub insertColumns()
Dim ws As Worksheet, i As Long
Set ws = ThisWorkbook.Worksheets(1)
For i = 2 To LastColumn(ws) * 2 Step 2
ws.Columns(i).Insert
Next
End Sub
Function LastColumn(ws As Worksheet, Optional rowNum As Long = 1)
With ws
LastColumn = .Cells(rowNum, .Columns.Count).End(xlToLeft).Column
End With
End Function

这将确定您有多少使用过的列，并循环遍历这些列-只需将LastColumn乘以2，因为您正在添加列。