求助指导取表格里的数

kulagen

donghui2363 发表于 2019-9-7 15:36
p5表也和p1表差不多，取出数来循环就行

怎么加

kulagen

donghui2363 发表于 2019-9-7 15:36
p5表也和p1表差不多，取出数来循环就行

怎么加

kulagen

donghui2363 发表于 2019-9-7 15:36
p5表也和p1表差不多，取出数来循环就行

怎么加

kulagen

donghui2363 发表于 2019-9-7 15:36
p5表也和p1表差不多，取出数来循环就行

怎么循环

donghui2363

总这么问也没个鲜花

kulagen

donghui2363 发表于 2019-9-8 08:05
总这么问也没个鲜花

不知道鲜花怎么弄？？看到了 刚看哈哈；是不是评分这里

kulagen

donghui2363 发表于 2019-9-8 08:05
总这么问也没个鲜花

大神指导下；循环

donghui2363

Sub 取数()
Dim wd As Workbook, ws As Worksheet
Dim cnn, sql$
Set cnn = CreateObject("adodb.connection")
Set rs = CreateObject("adodb.recordset")
Set ws = ActiveSheet
Set wd = ThisWorkbook
'------------------------
    mydir = Dir(ThisWorkbook.Path & "\数据源\" & "*.xlsx")
    Do While mydir <> ""
        If mydir = "表1.xlsx" Then
           cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0;Data Source=" & ThisWorkbook.Path & "\数据源\" & mydir
           For i = 1 To 12
            x = i & "月"
            sql = "select * from [" & x & "$hk14:ht15]"
            Set rs = cnn.Execute(sql)
            arr = rs.GetRows
            m = 0
            For k = 0 To UBound(arr)
                m = m + 1
                ws.Range("ab4").Offset(m, i) = arr(k, 0)
            Next k
           Next i
        End If
    mydir = Dir
    Loop
End Sub
p1表取数，你试试

kulagen

donghui2363 发表于 2019-9-8 10:02
Sub 取数()
Dim wd As Workbook, ws As Worksheet
Dim cnn, sql$

Sheet2.Cells(4, 3) = "=SUMPRODUCT((C$3=销量等材料!$ad$3:$ad$8000)*($A4=销量等材料!$z$3:$z$8000)*(销量等材料!$e$3:$e$8000))"
    Range("C4").Select
    Selection.AutoFill Destination:=Range("C4:t4"), Type:=xlFillDefault
    Range("C4:t4").Select
    Selection.AutoFill Destination:=Range("C4:t203"), Type:=xlFillDefault
    Range("C4:t203").Select
    Selection.Copy '复制
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False '数值化
    Application.CutCopyMode = False 'ESC

Sheet2.Cells(4, 21) = "=SUMPRODUCT((C$3=销量等材料!$ad$3:$ad$8000)*($A4=销量等材料!$z$3:$z$8000)*(销量等材料!$f$3:$f$8000))"
    Range("u4").Select
    Selection.AutoFill Destination:=Range("u4:al4"), Type:=xlFillDefault
    Range("u4:al4").Select
    Selection.AutoFill Destination:=Range("u4:al203"), Type:=xlFillDefault
    Range("u4:al203").Select
    Selection.Copy '复制
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False '数值化
    Application.CutCopyMode = False 'ESC

Sheet2.Cells(4, 39) = "=SUMPRODUCT((C$3=销量等材料!$ad$3:$ad$8000)*($A4=销量等材料!$z$3:$z$8000)*(销量等材料!$g$3:$g$8000))"
     Range("am4").Select
     Selection.AutoFill Destination:=Range("am4:bd4"), Type:=xlFillDefault
     Range("am4:bd4").Select
     Selection.AutoFill Destination:=Range("am4:bd203"), Type:=xlFillDefault
     Range("am4:bd203").Select
     Selection.Copy '复制
     Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
     :=False, Transpose:=False '数值化
     Application.CutCopyMode = False 'ESC

Sheet2.Cells(4, 57) = "=SUMPRODUCT((C$3=销量等材料!$ad$3:$ad$8000)*($A4=销量等材料!$z$3:$z$8000)*(销量等材料!$h$3:$h$8000))"
     Range("be4").Select
     Selection.AutoFill Destination:=Range("be4:bv4"), Type:=xlFillDefault
     Range("be4:bv4").Select
     Selection.AutoFill Destination:=Range("be4:bv203"), Type:=xlFillDefault
     Range("be4:bv203").Select
     Selection.Copy '复制
     Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
     :=False, Transpose:=False '数值化
     Application.CutCopyMode = False 'ESC

这三段怎么循环？
如果要cells里列+18循环到363结束？；怎么从E循环到Z结束？

donghui2363

Sheet2是哪个工作簿的表？刚才发的p1的是否能用