Excel启用vb编辑,要在选项中自定义功能区把开发工具加上。
上次简单使用vb处理一些excel数据,现学了一下。把代码存着,以后备用。
' 定义按钮点击事件
Private Sub CommandButton1_Click()
' 选择当前工作簿1
Worksheets(1).Select
' 如果处理多数据时,就false掉excel的实时更新
Application.ScreenUpdating = False
' 定义变量int型
Dim line As Integer
Dim col As Integer
' 赋值变量
line = 1
col = 2
Dim high As Integer
' 获取表格最大行数
'high = Application.WorksheetFunction.CountA(Columns(1).EntireColumn)
' 循环遍历从1到1000
For i = 1 To 1000 'Step 3
' 定义字符串
Dim str As String
' 定义数组
Dim arr1() As String
'Dim arr2() As String
Dim arrLength1 As Integer
'Dim arrLength2 As Integer
' 获取表格值,并对表格赋值
'Cells(line, 2).Value = Cells(i, 1)
str = Cells(i, 1)
' if then else endif判断语句
If str = "" Then
line = line + 1
col = 2
Else
' 划分字符串
arr1() = Split(str, " ")
' 获取字符串长度
arrLength1 = UBound(arr1)
For j = 0 To 1 'arrLength1
' 表格赋值
Cells(line, col).Value = arr1(j)
col = col + 1
' For循环结束标志
Next
Cells(line, col).Value = "v"
col = col + 1
Cells(line, col).Value = "-"
col = col + 1
End If
'arr1() = Split(str, " ")
'arrLength1 = UBound(arr1)
'If 0 = i Mod 2 Then
' For j = 0 To 1 'arrLength1
' Cells(line, col).Value = arr1(j)
' col = col + 1
' Next
'End If
'str = Cells(i + 2, 1)
'arr2() = Split(str, " ")
'arrLength2 = UBound(arr2)
'If 0 = i Mod 2 Then
' For m = 0 To arrLength2
' Cells(line, 2 + arrLength1 + 1 + m + 1).Value = arr2(m)
' Next
'End If
'Cells(line, 2 + arrLength1 + 1 + arrLength2 + 1 + 1).Value = Cells(i + 3, 1)
'line = line + 1
' For循环结束标志
Next
' 当所有数据处理完后,使excel刷新表格
Application.ScreenUpdating = True
' 函数结束
End Sub