excel VBA自定义列,然后自动平均分布总列宽
*******请教大虾,下列代码哪里存在问题? 运行显示 error 6,溢出问题,同时不知道是否存在其它错误******
'定义变量,变量类型已测试过 long,double,variant
Public i, j, k, widths, widthaver As long
'一个文本框给予输入起始列,如1
Private Sub TextBox1_Change()
i = TextBox1.Text
End Sub
'一个文本框给予输入终止列,如3
Private Sub TextBox2_Change()
j = TextBox2.Text
End Sub
'按下按钮触发列宽的计算,然后平均分配给每列
Private Sub CommandButton1_Click()
k = 0
widths = 0
For i = i To j
widths = Columns(i).ColumnWidth + widths
k = k + 1
Next i
widthaver = widths / k
For i = i To j
Columns(i).ColumnWidth = widthaver
Next i
End Sub
[解决办法]
你写的太混乱 改了下
'定义变量,变量类型已测试过 long,double,variant
Public lngBegin As Long
Public lngEnd As Long
Public lngRows As Long
Public widths As Long
Public widthaver As Long
'一个文本框给予输入起始列,如1
Private Sub TextBox1_Change()
lngBegin = TextBox1.Text
End Sub
'一个文本框给予输入终止列,如3
Private Sub TextBox2_Change()
lngEnd = TextBox2.Text
End Sub
'按下按钮触发列宽的计算,然后平均分配给每列
Private Sub CommandButton1_Click()
lngRows = 0
widths = 0
For i = lngBegin To lngEnd
widths = Columns(i).ColumnWidth + widths
lngRows = lngRows + 1
Next i
widthaver = widths / lngRows
For i = lngBegin To lngEnd
Columns(i).ColumnWidth = widthaver
Next i
End Sub