vba数组对比更新(Excel VBA数组比较脚本的问题)
本文目录
Excel VBA数组比较脚本的问题
Set d(i) = CreateObject("scripting.dictionary") 创建字典If d(1).Exists(arr1(i, 1)) And d(2).Exists(arr1(i, 1)) Then 如果同时存在
vba 对比大小代码
myarray = Array("B2_3", "B2_19")这个数组变量只有两个值,而 For i = 18 To 21 循环将运行4次,当i = 20 和 i = 21 时,myarray(i-18) 不就越界了吗?那么改的方法只有两个,要么是给 myarray 赋4个值,要么是减少循环次数。
VB将两个数组进行对比并输出其中相同的数据
专门帮你做了如下 程序,并经调试通过。加分啊!在程序中增加命令按钮一个,MSHFlexGrid四个,程序如下,Option ExplicitDim a(1 To 2000) As SingleDim b(1 To 2000) As SinglePrivate Sub Command1_Click() Dim i, j As Integer With MSHFlexGrid1 .Rows = 1 + UBound(a) .Cols = 2 For i = 1 To UBound(a) .TextMatrix(i, 1) = a(i) Next .Col = 1 .Sort = 1 ’排序 End With Call RemoveduplicateRows(MSHFlexGrid1) ’删除重复的行 With MSHFlexGrid2 .Rows = 1 + UBound(b) .Cols = 2 For i = 1 To UBound(b) .TextMatrix(i, 1) = b(i) Next .Col = 1 .Sort = 1 ’排序 End With Call RemoveduplicateRows(MSHFlexGrid2) ’删除重复的行 With MSHFlexGrid3 .Rows = MSHFlexGrid2.Rows + MSHFlexGrid1.Rows - 1 .Cols = 2 For i = 1 To MSHFlexGrid1.Rows - 1 ’’将前两表的数据合并到第三表 .TextMatrix(i, 1) = MSHFlexGrid1.TextMatrix(i, 1) Next For i = 1 To MSHFlexGrid2.Rows - 1 .TextMatrix(MSHFlexGrid1.Rows - 1 + i, 1) = MSHFlexGrid2.TextMatrix(i, 1) Next .Col = 1 .Sort = 1 ’排序 j = 1 MSHFlexGrid4.Rows = 2 MSHFlexGrid4.Cols = 2 For i = 2 To .Rows - 1 If .TextMatrix(i, 1) = .TextMatrix(i - 1, 1) Then MSHFlexGrid4.TextMatrix(MSHFlexGrid4.Rows - 1, 1) = .TextMatrix(i, 1) ’前第三表中重复的数据列在第四表,此第四表中第1列的数据就是你要的答案 MSHFlexGrid4.Rows = MSHFlexGrid4.Rows + 1 End If Next MSHFlexGrid4.Rows = MSHFlexGrid4.Rows - 1 End WithEnd SubPrivate Sub RemoveduplicateRows(MSH As MSHFlexGrid) ’删除重复的行 Dim i, j As Integer With MSH i = 1 Do j = i If .Rows 》 2 Then Do While j 《 .Rows - 1 If (.TextMatrix(i, 1) = .TextMatrix(j + 1, 1)) Or VBA.Len(.TextMatrix(j + 1, 1)) = 0 Then ’删除重复的行 .RemoveItem (j + 1) .Refresh j = j - 1 End If j = j + 1 Loop End If i = i + 1 Loop Until i 》= .Rows End WithEnd SubPrivate Sub Form_Load() Dim i As Integer Randomize For i = 1 To 2000 a(i) = Val(VBA.Format((Rnd(i) * 100), "#.##")) ’a(1 To 2000), b(1 To 2000)是你已经有的数据,这里由随机数代替 b(i) = Val(VBA.Format((Rnd(i) * 100), "#.##")) NextEnd Sub
EXCEL VBA 单元格与数组中数据对比,返回值并导出1新表
看上去变化就是a列的 "、"和后面的去掉简单点的做法就是,b列前插入空白列,将a列以"、"分列,之后删除插入的列,另存为就可以了一定要VBA的话sub test()arr=range("a1:b" & Range("a65535").end(3).row)for i = 1 to ubound(arr)arr(i,1)=split(arr(i,1),"、")(0)nextwith workbooks.add.sheets(1).cells(1,1).resize(ubound(arr),2)=arr.SaveAs CreateObject("WScript.Shell").SpecialFolders("Desktop") & "\导出数据表.xls", 50.close trueend withend sub
用VBA数组对比行内容错在哪里
Sub kphd()Dim x, y As Integer, k As Integer Dim arr Dim arr1 Dim arr2() arr = Sheets("部门统计单").Range("a2").Resize(Sheets("部门统计单").Range("a65536").End(xlUp).Row, 6) arr1 = Sheets("总部统计单").Range("a2").Resize(Sheets("总部统计单").Range("a65536").End(xlUp).Row, 6) ReDim arr2(1 To UBound(arr) + UBound(arr1), 1 To 6) For y = 1 To UBound(arr1) For x = 1 To UBound(arr) For i = 1 To 6 If arr(x, i) 《》 arr1(y, i) Then Exit For Next If i 》 6 Then k = k + 1 For i = 1 To 6 arr2(k, i) = arr(x, i) Next Exit For End If Next x Next y Sheets("相同").Range("a2").Resize(k, 6) = arr2End Sub
更多文章:
nvidia geforce 410m(nvidia geforce 410M 是独显吗)
2024年7月28日 17:36