VBA二维动态数组的如何处理？

437153

一个Excel文件，简称E：A列是姓名，B列也是姓名

两个文件夹：
文件夹 C 放的是以姓名A明明的若干xlsx文件，但文件名并不是姓名，而是有前缀数字及下划线符号。
文件夹 D 情况一样

需求：
1. 核对 E 表中，A列姓名对应在C的文件，和B列姓名对应在D的文件
2. 分别提取C、D文件的姓名和金额　 字符格式：张三，火星公司：10%，已认捐
3. 核对C的姓名是否出现在D文件，如果出现判断金额是不是小于5


我的解决思路：1. 用【Dir()】和通配符 【*姓名.xls*】去搜索A在C的文件
2. 如果结果是唯一的则去搜索B在D的文件
3. 如果也找到了唯一
4. 则用【Workbooks.Open Filename:=】打开文件
5. 遍历非空单元格
6. 用【InStr(), "，")】查找逗号，如果有，就用【Left()】提取出姓名
7. 用【InStr(), "%")】查找百分号，如果有，就继续查冒号，然后用MID()取出数字
8. 用提取的姓名和数字，存入一个二维数组 arr(1 to x , 1 to 2)


我遇到的问题：
如上思路中1-7我都解决了，但是8这里总是错误，因为数组的行数是不确定的，我使用Redim Preserve arr 就下标越界，求大佬指导正确的处理方式！

hfwufanhf2006

1、动态数组只能动态修改列，不能动态修改行;
     redim preserve arr(100,1 to x)是可以的，但
     redim preserve arr(1 to x,100)就不行；
     我个人经常用的方法是定义一个很大的数组，比如 dim arr(5000,100)，它应该是在任何情况下都是用不完的。然后再使用一个计数器，来定义实际使用的数组个数，代码的大致结构如下：
     dim arr(1 to 5000,100)
     js=0    ‘计数器
     for i=1 to [a10000].end(3).row
          arr(i,1)=cells(i,1)
          arr(i,2)=cells(i,2)
          ....
          js=js+1       ’每使用一个数组，计数器+1
     next i

     如果后面对这个数组进行查询，则：
     for i to js            ‘只对计数器范围内的数组做查询
         if arr(i,1)=.... then
            ....
         endif
     next i
2、涉及文件操作的都很慢，快不了的。如果只是查询文件还是很快的，但打开文件就非常慢了。如果是一大堆零散的文件都要逐个做数据处理，没办法让它快。正确的做法是把数据集中在一个文件中，这才叫数据库；

437153

hfwufanhf2006 发表于 2020-3-24 23:42
1、动态数组只能动态修改列，不能动态修改行;
     redim preserve arr(100,1 to x)是可以的，但
     re ...

您好，这里根据实际使用了多少个数组，没办法得出怎么办呢？我看您的代码好像是获取最后一行。但我的实际情况是，没办法获得最后一行的行号，因为数据最终在那一列都不确定。

hfwufanhf2006

437153 发表于 2020-3-25 00:01
您好，这里根据实际使用了多少个数组，没办法得出怎么办呢？我看您的代码好像是获取最后一行。但我的实际 ...

应该有个基本的假设前提：数据是集中在一起的，不能是毫无规则的任意位置都有可能存在数据；在这个假设前提下，判断数据区域的方法：
1、主流方法是利用CurrentRegion来读取当前数据区域，是range类，教材上都是讲它的；
     这个属性比较全面，能动态判断一些很刁钻的区域，比你自己写的判断代码要容易使用些，大致上：
     dim rg as range
     set rg =[b2].CurrentRegion      '读取从b2开始的扩展区域，比如下面截图的灰色区域被当成一个整体来读取；

     要读取区域中的每一个单元格，通常是用 for each
     dim rng as range
     js=1
     for each rng inch rg
          arr(js)=rng.value       '把数据写入数组，这个数组是一维的，因为 for each 是“逐一”读取，不分行列；
          js=js+1
     next
     下面截图的灰色区域总共有45个单元格，可以用CurrentRegion.count得到；

     上面是把二维区域的数据写入了一维数组里，很多时候你可能不想这样，新手理解起来也有困难，所以下面的代码则是维持二维数组的结构：
     js1 = 0    '行计数器
     for i = 1 to rg.rows.count    '区域的总行数
           js1 = js1 + 1                      '行计数器先+1，表示从下标1开始存放
           js2 = 0                               '列计数器，后面也是先+1，从下标1开始存放数据
          for k = 1 to rg.columns.count     '区域的总列数
                js2 = js2 + 1
                arr(js1, js2) = cells(i + 1, k + 1)      '这里要仔细推敲，数组从b2开始，数组从下标1开始，所以行列都相差1
          next k
     next i
     这样写入数组中的内容就与原来的二维区域结构就是完全一致的，与一维数组比较，逻辑理解上要简单很多，但代码明显比for each长；

     下面的代码则是把数组写入到从第21行a列开始的连续区域，结构维持原样：
     for i = 1 to js1
          for k = 1 to js2
                cells(i + 20, k) = arr(i, k)   '从21行A列开始写入数组，原来的数据从b2开始，实际就是整体向下移动20行、左移1列的效果；
         next k
     next i
     通常循环是最常用的控制手段，很多初学者喜欢 range.resize 来填充，不是不行，我个人更偏爱循环，想做什么就做什么，灵活性更好。比如只写入数组中有内容的部分，空白的数组过滤掉，range.resize就搞不定了，上面的双层循环搭配 if 就能轻易做到；
2、我常常是用我自己的土方法，没什么技巧，就是一列列一行行来逐个判断，比如：
      数据从a2开始，行末尾和列末尾不知道：
         for i=2 to [a100000].end(3).row   '行末尾，数字100000可以灵活写，只要比最末行的数据多1行就行；
              for k=1 to [a2].end(2).column   ‘最末列，数据从a2开始，最末列在哪里不知道就可以用这个参数
                    arr(i-1,k)=cells(i,k)     
              next k
         next i

      与上面的方法比较，其最末行和最末列的规则是不一样的：
          方法1：截图中可以看出，只要行与列没有同时出现空白，都会被看成是连续的数据整体；
          方法2：end(3).row会获取从[a100000]单元格往上遇到的第一个非空白单元格，比如遇到的第一个非空单元格是[a90000]，假如[a80000]到[a85000]都是空白，但[a85001] 开始又有数据了，得到的结果也是最后的那个非空[a90000]，这与方法1的规则不同，在方法1里，currentregion只会读取到该区域的数据末尾[a79999]，因为单元格前缀[a2]限定了这个区域是从[a2】起始的，到[a79999】就结束了，从[85001】开始的新的数据区域需要用[a85001].currentregion来重新读取；


3、如果数据是规则而连续的，我还会使用一种简单的判断方法：
     for i=2 to 100000            '循环必须大于数据区域
          if isempty(cells(i,1)) then     '遇到第一个空单元格就结束循环
             exit for
          endif
          arr(js)=cells(i,1)
     next i

     第三种完全是我的土方法，其实不如[a10000].end(3).row来的更简单，这种方法的规则反而与currentregion比较象，在个别场合还是有使用价值的；

437153

hfwufanhf2006 发表于 2020-3-25 09:48
应该有个基本的假设前提：数据是集中在一起的，不能是毫无规则的任意位置都有可能存在数据；在这个假设前 ...

谢谢，我换一种思路解决了。
既然行是固定的，那我就列来处理了。