记录学习VBA过程的点点滴滴(每天进步一点点)

jiminyanyan

http://club.excelhome.net/forum.php?mod=viewthread&tid=811914
正则表达式中使用后向引用来做文本替换

jiminyanyan

随机抽取不重复
http://club.excelhome.net/forum. ... p;page=1#pid5819355
四楼

1. Dim i%, num%, rng
   
2. Dim m(100) As Boolean, n(1 To 8, 1 To 2)
   
3. With Sheet2
   
4. rng = .Range(.[a2], .[b65536].End(3))
   
5. End With
   
6. Randomize
   
7. For i = 1 To 8
   
8.     num = Int(UBound(rng) * Rnd) + 1
   
9.     Do While m(num) = True
   
10.             num = Int(UBound(rng) * Rnd) + 1
    
11.     Loop
    
12.     m(num) = True
    
13.     n(i, 1) = rng(num, 1): n(i, 2) = rng(num, 2)
    
14. Next i
    
15. [b4].Resize(8, 2) = n

复制代码

jiminyanyan

不打开工作薄取表中的数据
http://club.excelhome.net/thread-486833-1-1.html

jiminyanyan

TextToColumns 用法.......
Sub test()
Dim i%, r%, d, arr
Set d = CreateObject("scripting.dictionary")
Workbooks.Open ThisWorkbook.Path & "\检验单.xls"
arr = ActiveWorkbook.Worksheets(1).[a1].CurrentRegion
Workbooks("检验单.xls").Close
For i = 2 To UBound(arr)
    d(arr(i, 1)) = arr(i, 2) & "|" & arr(i, 3)
Next i
For i = 2 To [b65536].End(3).Row
    If d.exists(Cells(i, 2).Value) Then
        Cells(i, 3) = d(Cells(i, 2).Value)
        Cells(i, 3).TextToColumns OtherChar:="|"
    End If
Next i
End Sub

jiminyanyan

字典 array 用法.....
Sub test1()
Dim i%, r%, d, arr
Set d = CreateObject("scripting.dictionary")
Workbooks.Open ThisWorkbook.Path & "\检验单.xls"
arr = ActiveWorkbook.Worksheets(1).[a1].CurrentRegion
Workbooks("检验单.xls").Close
For i = 2 To UBound(arr)
    d(arr(i, 1)) = Array(arr(i, 2), arr(i, 3))
Next i
For i = 2 To [b65536].End(3).Row
    If d.exists(Cells(i, 2).Value) Then
        Cells(i, 3).Resize(, 2) = d(Cells(i, 2).Value)
    End If
Next i
End Sub

jiminyanyan

两个工作簿的数据对比
http://club.excelhome.net/thread-854645-1-1.html

1. Sub Macro1()
   
2.     Dim cnn As Object
   
3.     Dim Mypath As String
   
4.     Dim myTable As String
   
5.     Dim SQL$, s$, i&
   
6.     Set cnn = CreateObject("adodb.connection")
   
7.     Mypath = ThisWorkbook.Path & ""
   
8.     myTable = "[Sheet1$" & [a3].CurrentRegion.Address(0, 0) & "]"
   
9.     myTable = "[Sheet1$a3:r] where 考场 is not null"
   
10.     a = [C3:R3&""]
    
11.     For i = 1 To UBound(a)
    
12.         s = s & ",iif(a." & a(i) & "=b." & a(i) & ",'√','×')"
    
13.     Next
    
14.     SQL = "select a.考场,a.考号" & s & " from (select * from " & myTable & ") a left join (select * from [Excel 8.0;Database=" & Mypath & "录入员2.xls]." & myTable & ") b on a.考场=b.考场 and a.考号=b.考号"
    
15.     cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;Data Source=" & Mypath & "录入员1.xls"
    
16.     Range("A4:R65536").ClearContents
    
17.     [a4].CopyFromRecordset cnn.Execute(SQL)
    
18.     cnn.Close
    
19.     Set cnn = Nothing
    
20. End Sub
    

复制代码

jiminyanyan

Access中Dsum和Sum的区别？
http://club.excelhome.net/forum.php?mod=viewthread&tid=598048

jiminyanyan

如何合并文件夹内多工作簿的有效数据行（整合花名册）
http://club.excelhome.net/forum.php?mod=viewthread&tid=837155

jiminyanyan

理解排名SUM(1)的作用。
http://club.excelhome.net/forum.php?mod=viewthread&tid=841451