提取表数据

VBA | 2018-10-05 16:43:38 | 阅读 257 次 | 评论(0)
Sub m_loss()


Dim d_m, r, r_loss As Integer

Dim date_new As Date
r = Range("a65536").End(xlUp).Row
r_loss = Sheets("LOSS").Range("a65536").End(xlUp).Row

zyname = InputBox("請輸入廠名")
d_m = Int(InputBox("請輸入月份date_m")) '注意數據轉換  2003,2007 好像版本不一樣
Sheets("LOSS").Cells(r_loss + 4, 1) = zyname & "的" & d_m & "月清金損耗"
Sheets("LOSS").Cells(r_loss + 4, 1).Interior.ColorIndex = 4
Sheets("LOSS").Cells(r_loss + 4, 1).Font.Bold = True

j = 5
For i = r To 1 Step -1
   If Month(Cells(i, 3)) = d_m And Cells(i, 1).Value = "MX" Then
            Sheets("LOSS").Cells(r_loss + j, 1).Value = Cells(i, 1)  '
            Sheets("LOSS").Cells(r_loss + j, 2).Value = Cells(i, 2)  '
            Sheets("LOSS").Cells(r_loss + j, 3) = Cells(i, 3)         '日期
            Sheets("LOSS").Cells(r_loss + j, 4).Value = Cells(i, 11)      ' 金質
            Sheets("LOSS").Cells(r_loss + j, 5).Value = Cells(i, 5)        ' 重量
            Sheets("LOSS").Cells(r_loss + j, 6).Value = Cells(i, 10)       '   類別
            Sheets("LOSS").Cells(r_loss + j, 7).Value = Cells(i, 7)        '  理論值
            Sheets("LOSS").Cells(r_loss + j, 8).Value = Cells(i + 1, 12)       '   實際回收值
            j = j + 1
     End If
     
   If Month(Cells(i, 3)) < d_m Or Year(Cells(i, 3)) < Year(Cells(r, 3)) Then       '跨年或跨月就退出
   
   
     Exit For
     
     End If
     
Next
        
         Sheets("LOSS").Cells(r_loss + j, 6) = "合計"
         Sheets("LOSS").Cells(r_loss + j, 6).Font.Bold = True
         Sheets("LOSS").Cells(r_loss + j, 6).Interior.ColorIndex = 6

End Sub

文章评论,共0条
游客请输入验证码
浏览164789次
最新评论