【實(shí)例07-根據(jù)模版生成數(shù)據(jù)】Excel表格VBA編程實(shí)例 代碼分享
Private Sub CommandButton生成_Click()
'清空生成結(jié)果
? ? With ThisWorkbook.Worksheets("生成結(jié)果")
? ? ? ? .UsedRange.ClearFormats
? ? ? ? .UsedRange.ClearContents
? ? End With
? ? With ThisWorkbook.Worksheets("臨時表")
? ? ? ? .UsedRange.ClearFormats
? ? ? ? .UsedRange.ClearContents
? ? End With
'將模版復(fù)制到臨時表
Dim modelrange As String
With ThisWorkbook.Worksheets("操作界面")
? ? If Trim(.Cells(2, "C").Value) <> "" Then
? ? modelrange = Trim(.Cells(2, "C").Value)
? ? End If
End With
With ThisWorkbook.Worksheets("模版")
Dim addmodeladdress As String
addmodeladdress = .Range(modelrange).Cells(1).Address
.Range(modelrange).Copy ThisWorkbook.Worksheets("臨時表").Range(addmodeladdress)
End With
'循環(huán)填充數(shù)據(jù)
With ThisWorkbook.Worksheets("數(shù)據(jù)列表")
Dim i, imax, j, jmax
imax = .Cells(1000000, 1).End(xlUp).Row
jmax = .Cells(1, 1000).End(xlToLeft).Column
If i = 1 Then
Exit Sub
End If
Dim rmax As Long? ? '生成結(jié)果最大行
For i = 2 To imax
? ? For j = 1 To jmax
? ? ? ? If .Cells(1, j) <> "" Then
? ? ? ? ? ? If .Cells(i, j).Value <> "" Then
? ? ? ? ? ? ? ? ThisWorkbook.Worksheets("臨時表").Range(CStr(.Cells(1, j))).Value = .Cells(i, j).Value
? ? ? ? ? ? Else
? ? ? ? ? ? ? ? ThisWorkbook.Worksheets("臨時表").Range(CStr(.Cells(1, j))).Value = ""
? ? ? ? ? ? End If
? ? ? ? End If
? ? Next j
? ? '循環(huán)一行，就將結(jié)果復(fù)制到生成結(jié)果表
If i = 2 Then
ThisWorkbook.Worksheets("臨時表").Range(modelrange).Copy ThisWorkbook.Worksheets("生成結(jié)果").Cells(1, 1)
Else
rmax = ThisWorkbook.Worksheets("生成結(jié)果").UsedRange.Cells(ThisWorkbook.Worksheets("生成結(jié)果").UsedRange.Count).Row
ThisWorkbook.Worksheets("臨時表").Range(modelrange).Copy ThisWorkbook.Worksheets("生成結(jié)果").Cells(rmax + 1, 1)
End If
Next i
End With
'處理完成跳轉(zhuǎn)到生成結(jié)果表
ThisWorkbook.Worksheets("生成結(jié)果").Activate
End Sub
