如何用VBA实现合并计算

talentlwz

如题,急!!!

rV5Rmx64.rar (2.08 KB, 下载次数: 517)

2008-3-9 00:48 上传

点击文件名下载附件
如何用VBA实现合并计算

[此贴子已经被作者于2008-3-9 0:48:52编辑过]

talentlwz

高手们帮帮忙

shizx98

Sub Macro1()
Dim w, wknm$
wknm = ThisWorkbook.Name
w = Array("'[" & wknm & "]数据表1'!" & Application.ConvertFormula(Sheet1.UsedRange.Address, xlA1, xlR1C1, 1), "'[" & wknm & "]数据表2'!" & Application.ConvertFormula(Sheet2.UsedRange.Address, xlA1, xlR1C1, 1))
With Sheet3
    .UsedRange.Clear
    .[a1].Consolidate w, xlSum, 1, 1
    [a1] = Sheet1.[a1]
End With
End Sub

laoyebin

QUOTE:

以下是引用shizx98在2008-3-9 10:02:23的发言：
Sub Macro1()
Dim w, wknm$
wknm = ThisWorkbook.Name
w = Array("'[" & wknm & "]数据表1'!" & Application.ConvertFormula(Sheet1.UsedRange.Address, xlA1, xlR1C1, 1), "'[" & wknm & "]数据表2'!" & Application.ConvertFormula(Sheet2.UsedRange.Address, xlA1, xlR1C1, 1))
With Sheet3
    .UsedRange.Clear
    .[a1].Consolidate w, xlSum, 1, 1
    [a1] = Sheet1.[a1]
End With
End Sub

厉害

w = Array("'[" & wknm & "]数据表1'!" & Application.ConvertFormula(Sheet1.UsedRange.Address, xlA1, xlR1C1, 1), "'[" & wknm & "]数据表2'!" & Application.ConvertFormula(Sheet2.UsedRange.Address, xlA1, xlR1C1, 1)) 这句是什么意思啊,楼上能不能帮解释一下

shizx98

Consolidate 方法

将若干工作表中若干区域的数据合并计算至单个工作表上的单个区域。Variant 类型。

expression.Consolidate(Sources, Function, TopRow, LeftColumn, CreateLinks)

上式中的Sources      Variant 类型，可选。以字符串数组的形式给出合并计算的源，该数组中包含 R1C1-样式的引用记号。这些引用必须包含将要合并计算的工作表的完整路径。

而 w 就是以字符串数组的形式给出合并计算的源。这里1，是要求字符串数组；2，是要求R1C1-样式的引用记号；3.是要求有工作表的完整路径。ConvertFormula方法是把合并区域单元格地址转换成R1C1-样式。

laoyebin

谢谢

talentlwz

 谢谢！

wsri

我也来一个，SQL的！

Public Sub hb()
 Application.ScreenUpdating = False '关闭屏幕更新
    Application.EnableEvents = False   '关闭事件响应
    'Application.Interactive = False     '禁止所有的键盘输入和鼠标输入'
 Dim conn As New ADODB.Connection
     Dim rst As New ADODB.Recordset

   Dim sql As String
     Set conn = New ADODB.Connection
     Set rst = New ADODB.Recordset
  
    rq = Format(Now(), "yyyy-m-d")
    conn.Open "provider=microsoft.jet.oledb.4.0;extended properties=excel 8.0;data source = " & ThisWorkbook.FullName
   
   
        sql = "select A.姓名,max(A.工龄) AS 工龄,max(A.工资) AS 工资,max(A.补贴) as 补贴,max(A.住房费) as 住房费,max(A.Expr1005) as 伙食费  from " & _
    "(select 姓名,工龄,工资,补贴,住房费,'' from [数据表1$] " & _
          "Union " & _
          " select 姓名,工龄,工资,'','',伙食费 from [数据表2$]) A GROUP BY A.姓名"

    rst.Open sql, conn, adOpenKeyset, adLockOptimistic
 
 h = rst.RecordCount
 Sheets("汇总").Select
  For Each Field In rst.Fields
 Sheets("汇总").[a1].Offset(0, I) = Field.Name
 I = I + 1
 Next
 
 rowh = Sheets("汇总").Range("a65000").End(xlUp).Row
 
 Rows("2:" & rowh).Select
    Selection.ClearContents
 rowh = Sheets("汇总").Range("a65000").End(xlUp).Row
    Sheets("汇总").Range("A" & rowh).Offset(1, 0).CopyFromRecordset rst
   
    Range("A2").Select
conn.Close

Set conn = Nothing
 Application.Interactive = True
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub
非常棒！呵呵

Wz2796

看不懂……

山海风云轩

shizx98 发表于 2008-3-9 10:02
Sub Macro1()Dim w, wknm$wknm = ThisWorkbook.Namew = Array("'[" & wknm & "]数据表1'!" & Applica ...

厉害呀，666