vba formula 的带变量引用使用 多表汇总

VBA | 2018-09-30 09:50:51 | 阅读 200 次 | 评论(2)

Sub ai_insurance()

cr = Range("c65536").End(xlUp).Row

Set crng = Range(Cells(1, 3), Cells(cr, 3))

Dim arr(13) As Integer
    
 For Each c In crng
    Select Case Left(c, 2)
    
        Case "1月"
         arr(1) = c.Row
                  
        Case "2月"
         arr(2) = c.Row
         
        
        Case "3月"
         arr(3) = c.Row
        
         
         Case "4月"
         arr(4) = c.Row
            
            
         Case "5月"
         arr(5) = c.Row
            
            
         Case "6月"
         arr(6) = c.Row
            
            
         Case "7月"
         arr(7) = c.Row
            
           
         Case "8月"
         arr(8) = c.Row
         
        
         Case "9月"
         arr(9) = c.Row
         
         
         Case "10"
         arr(10) = c.Row
         
         
         Case "11"
         arr(11) = c.Row
         
        
         
         
          Case "12"
         arr(12) = c.Row
         
          End Select
         Next
         arr(0) = arr(1) - 5  '固定1月起始行号(?-4)
        begin_m = InputBox("请输入起始月 (1~ 11的数值)")
        
        end_m = InputBox("请输入结束月(大于起始月小于12的数值)")
      
        
        col = 0
        For i = begin_m To end_m 'i是月份,循环每月
            r = 0
            For j = arr(i - 1) + 1 To arr(i) - 1     'j是每月的内容行号
                Cells(cr + 1 + r, 5 + col).Formula = "=d" & (j)
                r = r + 1  '让当月数据每行递增
            Next
            col = col + 1  '让月份增加时列号增加
        Next


End Sub


y_from_m.xlsm (下载2次,上传于2018-09-30 09:50:51)


文章评论,共2条
Image
1楼: 贫造既让 发表于 2018-09-30 16:49   回复
Avatar
2楼: qunxingw 发表于 2018-09-30 18:06   回复
以下是引用贫造既让在2018-09-30 16:49的发言1

我怎么看不懂
结合附件,在分表A或B...实验一下宏就理解了
游客请输入验证码
浏览161930次
最新评论