Excel精英培训网

 找回密码
 注册
数据透视表40+个常用小技巧,让你一次学会!
12
返回列表 发新帖
楼主: redsunsky

依据公司名称和相应的产品数量值,插入多行空白行

[复制链接]
发表于 2020-4-4 10:41 | 显示全部楼层
我给你说下关键点:
1、因为插入行是不确定的,不断插入行,会导致最末行[c10000].end(3).row这个通常的用法不成立,因此需要直接写一个足够大的数字:
     For i = 3 To 1000      '1000就是一个足够大的数字
2、因为没有.end(3).row参数,所以需要有结束的标志,下面是结束最通常的做法,当然还有其他方法:
     If IsEmpty(Cells(i, 3)) Then    ’c列为空退出
        Exit For
     End If
3、插入通常是插入在当前单元格之前,当前单元格是:
     Cells(i, "c").Select
     然后要在它后面插入,所以才有:
     Range("c" & i + 1 & ":d" & ii + i).Insert Shift:=xlDown
     注意 c(i+1)就是cells(i,"c")的下一行,而区域大小 c(i+1):d(ii+i)就是选择的区域,插入的数量与选择的区域是相等的,你手工操作一下就知道了,选多大的区域,插入的区域也会是一摸一样的大;
4、最后因为有插入的空行,我们需要把循环参数i重新定位到下一个有可能需要插入的行,所以需要跳行:
     i = i + ii
     这个动作的目的是让插入能够延续,不然经过 If IsEmpty(Cells(i, 3)) Then 判断,代码就结束了;

在上述理解的基础上,你再根据自己的需要更改代码;

评分

参与人数 1学分 +2 收起 理由
redsunsky + 2 感恩老师这么细心教,因为我就这个年假自学.

查看全部评分

excel精英培训的微信平台,每天都会发送excel学习教程和资料。扫一扫明天就可以收到新教程
回复

使用道具 举报

 楼主| 发表于 2020-4-4 13:13 | 显示全部楼层
hfwufanhf2006 发表于 2020-4-4 10:23
1、你代码明确排除了1的可能性:
        If ii >= 2 Then
        Range("c" & i + 1 & ":d" & ii + i ...

老师原本您写的是正确能使用的,因为我实际应用时有点变化,所以想改动了一下。老师您写的原代码:
ii = Cells(i, "D")  
        Range("c" & i + 1 & ":d" & ii + i).Insert Shift:=xlDown
    End If
    i = i + ii
Next i

例如A公司对应有2个产品,效果是A公司下加面加插了2行。
  C列          D列
A公司
1行
2行
现在我想是如果A公司对应的有2个产品时,只需加1行就够。
即是效果
A公司
1行
我在老师您的原代码上加了-1.能实现。
ii = Cells(i, "D")  
        Range("c" & i + 1 & ":d" & ii + i -1).Insert Shift:=xlDown
    End If
    i = i + ii -1


但是问题来了:
1、下面的公司例如B公司或者C公司,有可能就只有对应1个产品时。
上述的代码运行到B公司时,就卡在那里,不能再往下面循环了。
上述代码只要对应产品为2个时,是没有问题。只有1时就有问题,
看看如果再修正些。


我想:是否可实现这样?
原信息
A公司 有2个产品
B公司 有1个产品
C公司 有3个产品


想实现为
A公司
加插1行空白行
B公司 (不动)
C公司
加插2行空白行

我试写了:
Sub test()
For i = 3 To 1000
    If IsEmpty(Cells(i, 3)) Then
       Exit For
    End If
    Cells(i, "c").Select
    If Cells(i, "C") <> "" Then
        ii = Cells(i, "D")
        If ii >= 2 Then
        Range("c" & i + 1 & ":d" & ii + i - 1).Insert Shift:=xlDown
           i = i + ii - 1
        End If
        If ii = 1 Then   ‘如果产品为1时,不加插,没有什么操作,就加多I+1 ,进入下一个数的循环。
           i = i + ii         ’我估计我这罗辑有问题,跳过去。
        End If
    End If
Next i
End Sub





回复

使用道具 举报

发表于 2020-4-4 18:23 | 显示全部楼层
redsunsky 发表于 2020-4-4 13:13
老师原本您写的是正确能使用的,因为我实际应用时有点变化,所以想改动了一下。老师您写的原代码:
ii = ...

你是希望所有的插入行都比所标定的数字小1,当数字是1时就不插入?那代码可以更简单:
For i = 3 To 1000
    If IsEmpty(Cells(i, 3)) Then
       Exit For
    End If
    Cells(i, "c").Select
    If Cells(i, "d") > 1 Then    '数字大于1时插入,如果是1,正好利用for自然循环到下一行,不需要额外的跳行
        ii = Cells(i, "D")
        Range("c" & i + 1 & ":d" & ii + i - 1).Insert Shift:=xlDown
        i = i + ii - 1
    End If
Next i

评分

参与人数 1学分 +2 收起 理由
redsunsky + 2 实在感激!和拜谢无言感激

查看全部评分

回复

使用道具 举报

 楼主| 发表于 2020-4-4 22:35 | 显示全部楼层
hfwufanhf2006 发表于 2020-4-4 18:23
你是希望所有的插入行都比所标定的数字小1,当数字是1时就不插入?那代码可以更简单:
For i = 3 To 100 ...

真心的感恩老师您的指点。因为我也是今年年假借着在家的时间,自习了一下VBA。后来发现好有用和兴趣。我也不想做伸手党,也经常自已思考。知识局限了我的思维。无奈看些书都是好比较表面。缺少练习。。杨请教老师有好的建议,我该如何可以加强一下这方面的练习?好像网上也没有什么书是练习题和讲解。还望指点一下,如果能一步步把基础学习好。感恩合十!
回复

使用道具 举报

发表于 2020-4-4 22:43 | 显示全部楼层
本帖最后由 金樽空对月 于 2020-4-4 22:50 编辑
  1. Sub myinsert()
  2. Dim x As Integer
  3. For x = Range("d65536").End(xlUp).Row To 3 Step -1
  4.   If Cells(x, 4) > 1 Then
  5.      Range("c" & x + 1 & ":d" & x + Cells(x, 4) - 1).Select
  6.      Selection.Insert shift:=xlDown
  7.      Range("c" & x & ":d" & x + Cells(x, 4) - 1).Borders.LineStyle = True
  8.   End If
  9. Next
  10. End Sub
复制代码


jdfw.gif

技术请教2.zip

15.39 KB, 下载次数: 2

评分

参与人数 1学分 +2 收起 理由
redsunsky + 2 谢谢您们的热心帮助。

查看全部评分

回复

使用道具 举报

发表于 2020-4-5 13:00 | 显示全部楼层
redsunsky 发表于 2020-4-4 22:35
真心的感恩老师您的指点。因为我也是今年年假借着在家的时间,自习了一下VBA。后来发现好有用和兴趣。我 ...

每个人的方法都不同,我只能根据我个人的学习体会讲一下:1、基础的东西一定要学好,这个不能省,比如:
     工作簿的控制:thisworkbook;
     工作表的控制:worksheets("sheet1")或者worksheets(n),n是工作表序号;
     单元格的控制:range("a1:b10")或者range(cells(1,1),cells(10,2)),推荐后者,配合循环很合适;
     当前工作表:就是代码运行前眼睛能看见的工作表,也是代码所在的工作表,可以省略前缀直接引用单元格,所以:
           当前工作表可以这么引用:cells(x,y)
           非当前工作表只能这么引用:thisworkbook.worksheets("sheets").cells(x,y),如果只有一个工作簿打开,thisworkbook可以省略;
     上述内容在vba的help里“excel开发人员参考-概念-工作簿与工作表”以及“单元格和区域”章节都有详细的叙述,建议先看;

2、代码的基础流程控制要掌握好,最常用的就是 for next循环以及 if 条件,尤其是for循环。很多人会走弯路,把注意力放在了单元格填充.resize或者offset这类属性方法上面了,这些工作for循环都能办到,当然效率不见得比resize高。这里的重点是for有助于锻炼你的逻辑思维和流程控制,而resize只是一个矩形填充,毫无技术可言,多数情况下都是有条件的填充,所以for+if比较好用,resize则很有局限性;

3、数组,如果再深入一点可以学习字典。先说数组:
     1)数组与for之间也是十分融洽,一维数组与单层for搭配,二维数组与双层for搭配,天衣无缝;
         比如函数sum求和,代码也能很容易写出来;
         for i to 10     '1到10行
             for k=1 to 5  '1到5列
                  hj=hj+cells(i,k)
             next k
         next i
         数组与上面的很相似:
         for i to 10     '1到10行
             for k=1 to 5  '1到5列
                  ' if   条件  then
                        cells(i,k)=arr(i,k)      '把数组写入单元格,用resize确实更简单,这里只是强调可以条件填充,resize则不行
                 ' endif
             next k
         next i

     2)字典
         字典并非必须,字典可以用数组代替,但字典在大数据的效率很高,数据量越大,效率差异越大。我测试3000条记录两者差距不明显,5000记录以上建议用字典,50000记录基本上会有3-5倍甚至更大的效率差异;
         这两者放在一起比较,基本都是用于查询,不过数组除了查询,用途更广泛,字典只是查询,偶尔也能用于统计;
         常见的查询场景:
             A区域的数据                 B区域的数据
             查询A区域是否在B区域存在,并把对应结果返回。如果用工作表函数vlookup也能做大部分工作,只是vlookup局限性大,尤其效率很低;

         单元格查询(不推荐,也要了解,1000记录以内效率也可以,最多可以放宽到3000-5000记录的查询):
            for i=1 to [a10000].end(3).row     ‘循环a区域
                 s=cells(i,1)                            '读取a列
                 for k=1 to [h10000].end(3).row     ‘循环b区域,假设从h列开始
                     if cells(k,"h")=s then                 '比较
                        cells(i,2)=cells(k,"i")              ’把b区域的I列结果返回到A区域的B列
                     endif
                 next k
              next i

         数组查询,仍以上述区域为例,代码结构类似,只是效率更高:
            dim arr1
            dim arr2
            arr1=range(cells(1,1),cells([a100000].end(3).row,2))      ‘把A区域写入数组arr1,是二维数组,单元格区域都是二维的
            arr2=range(cells(1,"h"),cells([h100000].end(3).row,"i"))      ‘把B区域写入数组arr2
            for i=1 to ubound(arr1)     ‘循环a区域

                 for k=1 to ubound(arr2)     ‘循环b区域
                     if arr1(i,1)=arr2(k,1) then         '比较
                        cells(i,2)=cells(k,"i")              ’直接写单元格,不需要先用数组来过度。数组与单元格的坐标是对应的;
                     endif
                 next k
             next i

         字典查询,效率最高:
         dim zd as object
         set zd=createobject("scripting.dictionary")
         for i=1 to [h10000].end(3).row     ‘先把a区域写入字典
             s=cells(i,"h")
             zd(s)=cells(i,"i")       ’字典左边是查询关键字,也就是h列,右边是内容,也就是i列
         next i
         for i=1 to [a10000].end(3).row     ‘循环a区域
              s=cells(i,1)
              if zd.exists(s) then                  ‘字典存在就表示B区域存在,exists方法是速度快的主要原因,不管多少记录,瞬间即可得到;
                 cells(i,2)=zd(s)                   ’字典写入b列,字典的内容就是i列的内容,zd(s)是zd.item(s)的简写,是内容不是关键字索引
              endif
         next i

         还有一种效率最高(我个人认为是这样的)的查询,那就是sql查询,这里就不展开了

4、上面我把单元表格、数组以及字典查询详细说了一遍,是因为这类场景非常多见,这是最基础的东西了;
     下一步,我建议了解 help-vba语言参考,这是一个字典工具,如果你不知道如何用,可以到这里查help,这里什么都有;

5、还有百度,个人强烈建议使用,比如:
     如果不知道如何使用字典,在百度输入”vba字典“,各种用法全都有;
     如果要文件检索代码,输入”vba文件检索“,你可以随便抄一段下来自己用;
     如果要学习最高效的sql,输入vba SQL,别人的代码随便抄一段下来自己先研究;
     只要是vba三个字母开头,基本上所有的vba用法都能找到答案;







评分

参与人数 1学分 +2 收起 理由
redsunsky + 2 感恩老师您!

查看全部评分

回复

使用道具 举报

 楼主| 发表于 2020-4-5 16:47 | 显示全部楼层
hfwufanhf2006 发表于 2020-4-5 13:00
每个人的方法都不同,我只能根据我个人的学习体会讲一下:1、基础的东西一定要学好,这个不能省,比如:
...

实在感恩老师这么用心分享。实到好到位。FOR IF 对于我这初学者,确实需要点时间积累对FOR,IF的理解。
回复

使用道具 举报

您需要登录后才可以回帖 登录 | 注册

本版积分规则

小黑屋|手机版|Archiver|Excel精英培训 ( 豫ICP备11015029号 )

GMT+8, 2024-3-29 10:37 , Processed in 0.643228 second(s), 18 queries , Gzip On, Yac On.

Powered by Discuz! X3.4

Copyright © 2001-2020, Tencent Cloud.

快速回复 返回顶部 返回列表