标题: [求助] vba代码的编写 [打印本页] 作者: luanyu6688 时间: 2007-1-2 20:52 标题: [求助] vba代码的编写 vba代码的编写,各位大虾,请看看我的附件,帮我一下,如何做这个工作表.谢谢[attach]22394[/attach] 作者: fjzjyan 时间: 2007-1-24 05:35 标题: 从不同工作表中动态统计数据 1、将余额表第一行余数的数据改为同月汇表E2格式同，分别将月汇表、余额表、销售表的工作表代码名称设为ws1、ws2、ws3.
2、按Alt+F11进入VBE界面，插入一个模块并在其中编写以下2个子例程
Sub tjye(rg As Range)
Dim i As Integer, j As Integer, x As Integer
Dim rg3 As Range, rg1 As Range, rg2 As Range
Dim rg4 As Range, rg5 As Range, n As Integer
x = ws2.[a1].CurrentRegion.Rows.Count
Set rg1 = ws2.Range(ws2.Cells(1, 1), ws2.Cells(x, 1))
Set rg4 = ws2.Range(ws2.Cells(1, 5), ws2.Cells(1, 16))
Set rg2 = ws1.[a4]
Set rg5 = rg4.Find(rg.Value, lookat:=xlWhole)
n = rg5.Offset(0, -1).Column
For j = 1 To 8
Set rg3 = rg1.Find(rg2.Value, lookat:=xlWhole)
rg2.Offset(0, 4).Value = rg3.Offset(0, n - 1).Value
Set rg2 = rg2.Offset(1, 0)
Next j
End Sub
Sub tjxs(rg As Range)
Dim i As Integer, j As Integer, x As Integer, n As Integer
n = ws3.[a1].CurrentRegion.Rows.Count
For i = 4 To 11
ws1.Cells(i, 8) = 0
ws1.Cells(i, 6) = 0
ws1.Cells(i, 7) = 0
For j = 2 To n
If ws3.Cells(j, 1) = ws1.Cells(i, 1) And Month(ws3.Cells(j, 2)) = Month(rg) And Year(ws3.Cells(j, 2)) = Year(rg) Then
ws1.Cells(i, 6) = ws1.Cells(i, 6) + ws3.Cells(j, 6)
ws1.Cells(i, 7) = ws1.Cells(i, 7) + ws3.Cells(j, 7)
ws1.Cells(i, 8) = ws1.Cells(i, 8) + ws3.Cells(j, 8)
End If
Next j
Next i
End Sub
3、在月汇表工作表change事件中编写如下代码。
Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Target.Address
Case "$E$2"
tjye Target
tjxs Target
End Select
End Sub
4、其他部分的子例程的编写与以上雷同，只在工作表change事件中加到select case 分支语句即可。