[求助] vba代码的编写

luanyu6688

vba代码的编写,各位大虾,请看看我的附件,帮我一下,如何做这个工作表.谢谢

fjzjyan

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 分支语句即可。

5、建议：多表相关最好在ACCESS建数据表和查询，用EXCELVBA代码及ADO对象提取ACCESS数据开发统计表单与动态查询，简洁、高效、实用。几天的工作只需几分钟即可完成。

6、 我们长期从事于Excel VBA+Access数据库开发等课程的教学工作。并以Visual Basic为开发工具，以SQL Sever（或Microsoft  Access）为后台数据库，用Microsoft  Excel  VBA开发输出的报表和表单为珠三角一些中小型企业开发多个生产管理、MRP系统。
如果你想提高EXCEL VBA开发，可联系我们（电话：13713090487  QQ：522519200 邮箱：fj_zjyan@163.com)，通过远程协助让你足不出户便学到你急需的知识。如果你想信息化管理可以将你的需求完整发给我们，我们可为你量身定制开发一套你所需系统给你试用。

[此贴子已经被作者于2007-1-23 21:39:13编辑过]