注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

零售创新,创新那些事儿,SPSS,VBA

零售创新

 
 
 

日志

 
 
关于我

新浪微博,零售创新 研究经理,数据分析师 希望和市场研究和零售业的同事共同进步! 本博客发表的都是免费或试用的资料,如果有版权问题请发邮件wangli12a@163.com联系删除。 spss excel vba blog

网易考拉推荐

删除代码宏  

2009-02-26 09:04:59|  分类: VB和VBA知识 |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |
Sub dele()
Dim i As Long
Application.ScreenUpdating = False
For i = [a65536].End(xlUp).Row To 3 Step -1
    If Cells(i, 5) < 5 Then
        Rows(i).Delete
    End If
    
        If Cells(i, 1) Like "*" & "汇总" Then
        Rows(i).Delete
    End If
    
    If Cells(i, 2) Like "*" & "汇总" Then
        Rows(i).Delete
    End If
    
    If Cells(i, 3) Like "*" & "汇总" Then
        Rows(i).Delete
    End If
    
Next

End Sub











Dim a(228), b, c1, d, e


Sub 合库()
'
' 合库 Macro
' hp 记录的宏 2008-4-21
'

'

For i = 1 To 55
a(i) = Sheets("sum").Cells(i, 1)

Next

  Sheets("sum").Select

Range("B1").Select
    ActiveCell.FormulaR1C1 = "=COUNT(合肥!C[-1])"
    Range("B2").Select
    ActiveCell.FormulaR1C1 = "=COUNT(北京!C[-1])"
    Range("B3").Select
    ActiveCell.FormulaR1C1 = "=COUNT(福州!C[-1])"
    Range("B4").Select
    ActiveCell.FormulaR1C1 = "=COUNT(泉州!C[-1])"
    Range("B5").Select
    ActiveCell.FormulaR1C1 = "=COUNT(厦门!C[-1])"
    Range("B6").Select
    ActiveCell.FormulaR1C1 = "=COUNT(兰州!C[-1])"
    Range("B7").Select
    ActiveCell.FormulaR1C1 = "=COUNT(西宁!C[-1])"
    Range("B8").Select
    ActiveCell.FormulaR1C1 = "=COUNT(广州!C[-1])"
    Range("B9").Select
    ActiveCell.FormulaR1C1 = "=COUNT(佛山!C[-1])"
    Range("B10").Select
    ActiveCell.FormulaR1C1 = "=COUNT(东莞!C[-1])"
    Range("B11").Select
    ActiveCell.FormulaR1C1 = "=COUNT(汕头!C[-1])"
    Range("B12").Select
    ActiveCell.FormulaR1C1 = "=COUNT(深圳!C[-1])"
    Range("B13").Select
    ActiveCell.FormulaR1C1 = "=COUNT(中山!C[-1])"
    Range("B14").Select
    ActiveCell.FormulaR1C1 = "=COUNT(南宁!C[-1])"
    Range("B15").Select
    ActiveCell.FormulaR1C1 = "=COUNT(贵阳!C[-1])"
    Range("B16").Select
    ActiveCell.FormulaR1C1 = "=COUNT(保定!C[-1])"
    Range("B17").Select
    ActiveCell.FormulaR1C1 = "=COUNT(石家庄!C[-1])"
    Range("B18").Select
    ActiveCell.FormulaR1C1 = "=COUNT(唐山!C[-1])"
    Range("B19").Select
    ActiveCell.FormulaR1C1 = "=COUNT(邯郸!C[-1])"
    Range("B20").Select
    ActiveCell.FormulaR1C1 = "=COUNT(郑州!C[-1])"
    Range("B21").Select
    ActiveCell.FormulaR1C1 = "=COUNT(哈尔滨!C[-1])"
    Range("B22").Select
    ActiveCell.FormulaR1C1 = "=COUNT(武汉!C[-1])"
    Range("B23").Select
    ActiveCell.FormulaR1C1 = "=COUNT(长沙!C[-1])"
    Range("B24").Select
     ActiveCell.FormulaR1C1 = "=COUNT(长春!C[-1])"
        Range("B25").Select
     ActiveCell.FormulaR1C1 = "=COUNT(常州!C[-1])"
        Range("B26").Select
     ActiveCell.FormulaR1C1 = "=COUNT(南京!C[-1])"
        Range("B27").Select
     ActiveCell.FormulaR1C1 = "=COUNT(苏州!C[-1])"
        Range("B28").Select
     ActiveCell.FormulaR1C1 = "=COUNT(无锡!C[-1])"
        Range("B29").Select
     ActiveCell.FormulaR1C1 = "=COUNT(南通!C[-1])"
        Range("B30").Select
     ActiveCell.FormulaR1C1 = "=COUNT(徐州!C[-1])"
        Range("B31").Select
     ActiveCell.FormulaR1C1 = "=COUNT(南昌!C[-1])"
        Range("B32").Select
     ActiveCell.FormulaR1C1 = "=COUNT(大连!C[-1])"
        Range("B33").Select
     ActiveCell.FormulaR1C1 = "=COUNT(沈阳!C[-1])"
        Range("B34").Select
     ActiveCell.FormulaR1C1 = "=COUNT(济南!C[-1])"
        Range("B35").Select
     ActiveCell.FormulaR1C1 = "=COUNT(青岛!C[-1])"
        Range("B36").Select
     ActiveCell.FormulaR1C1 = "=COUNT(烟台!C[-1])"
        Range("B37").Select
     ActiveCell.FormulaR1C1 = "=COUNT(潍坊!C[-1])"
        Range("B38").Select
     ActiveCell.FormulaR1C1 = "=COUNT(西安!C[-1])"
        Range("B39").Select
     ActiveCell.FormulaR1C1 = "=COUNT(上海!C[-1])"
        Range("B40").Select
     ActiveCell.FormulaR1C1 = "=COUNT(成都!C[-1])"
        Range("B41").Select
     ActiveCell.FormulaR1C1 = "=COUNT(天津!C[-1])"
        Range("B42").Select
     ActiveCell.FormulaR1C1 = "=COUNT(乌鲁木齐!C[-1])"
        Range("B43").Select
     ActiveCell.FormulaR1C1 = "=COUNT(昆明!C[-1])"
        Range("B44").Select
     ActiveCell.FormulaR1C1 = "=COUNT(杭州!C[-1])"
        Range("B45").Select
     ActiveCell.FormulaR1C1 = "=COUNT(金华!C[-1])"
        Range("B46").Select
     ActiveCell.FormulaR1C1 = "=COUNT(宁波!C[-1])"
        Range("B47").Select
     ActiveCell.FormulaR1C1 = "=COUNT(绍兴!C[-1])"
        Range("B48").Select
     ActiveCell.FormulaR1C1 = "=COUNT(台州!C[-1])"
        Range("B49").Select
     ActiveCell.FormulaR1C1 = "=COUNT(温州!C[-1])"
        Range("B50").Select
     ActiveCell.FormulaR1C1 = "=COUNT(嘉兴!C[-1])"
        Range("B51").Select
     ActiveCell.FormulaR1C1 = "=COUNT(重庆!C[-1])"
        Range("B52").Select
     ActiveCell.FormulaR1C1 = "=COUNT(海口!C[-1])"
        Range("B53").Select
     ActiveCell.FormulaR1C1 = "=COUNT(呼和浩特!C[-1])"
        Range("B54").Select
     ActiveCell.FormulaR1C1 = "=COUNT(银川!C[-1])"
        Range("B55").Select
     ActiveCell.FormulaR1C1 = "=COUNT(太原!C[-1])"
     



For i = 1 To 55


d = Sheets("sum").Cells(i, 2)


If d = 0 Then
Else
Sheets("ALL").Select

S1 = "A" & 2
S2 = "Q" & (d + 1)
S3 = ":"
s4 = S1 + S3 + S2
S6 = "A"
S8 = S6 + S3 + S6
S5 = WorksheetFunction.Count(Range(S8))
If S5 = 0 Then
S5 = 2
Else
S5 = (WorksheetFunction.Count(Range(S8)) + 2)
End If


S7 = S6 & S5
Sheets(a(i)).Select

Range(s4).Select
    Selection.Copy
  Sheets("ALL").Select
Range(S7).Select
 ActiveSheet.Paste



  End If

Next



End Sub

  评论这张
 
阅读(795)| 评论(0)
推荐 转载

历史上的今天

在LOFTER的更多文章

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2017